B4J Question [Solved] SqlLite , How Select data per month ??

[behnam_tr]

hello my friends

I have the following database table and I want to extract the Monthly profit (from the profit column) information with the optimal code.
Does anyone have a solution ??

In fact, how many profit did we have per month?

like this :

Month 1 >> 250,000 $
Month 2 >> 300,000 $
.
.
.
.
.

all date values stored with DateTime.Now
We assume that the information has been stored from 1/1/2021 until today

thanks.

 

[aeric]

I think you need to convert datetime in tick to month and use aggregate function SUM with GROUP BY to get the results.

 

[EnriqueGonzalez]

How to convert milliseconds to date in SQLite

I store date from Calendar.getTimeInMilliseconds() in SQLite DB. I need to mark first rows by every month in SELECT statement, so I need convert time in milliseconds into any date format using SQLite

stackoverflow.com

Do this to your date column and in a second query use it to group by month

 

[behnam_tr]

How to convert milliseconds to date in SQLite

I store date from Calendar.getTimeInMilliseconds() in SQLite DB. I need to mark first rows by every month in SELECT statement, so I need convert time in milliseconds into any date format using SQLite

stackoverflow.com

Do this to your date column and in a second query use it to group by month

many thanks
after many search and test i write this query
and its work fine

    Dim RS As ResultSet=Sql1.ExecQuery($"SELECT strftime('%Y', date / 1000, 'unixepoch') as year,
strftime('%m', date / 1000, 'unixepoch') as month,
SUM( profit ) AS sum FROM tbl2 GROUP BY month;"$)
    Do While RS.NextRow
   
        Log(RS.GetString("year"))
        Log(RS.GetString("month"))
        Log(RS.GetString("sum"))
        Log("====================")
    Loop
    RS.Close

*** if you used UnixTime you should use this
strftime('%Y', date)

 

[aeric]

many thanks
after many search and test i write this query
and its work fine

    Dim RS As ResultSet=Sql1.ExecQuery($"SELECT strftime('%Y', date / 1000, 'unixepoch') as year,
strftime('%m', date / 1000, 'unixepoch') as month,
SUM( profit ) AS sum FROM tbl2 GROUP BY month;"$)
    Do While RS.NextRow
 
        Log(RS.GetString("year"))
        Log(RS.GetString("month"))
        Log(RS.GetString("sum"))
        Log("====================")
    Loop
    RS.Close

*** if you used UnixTime you should use this
strftime('%Y', date)

If you have data more than 1 year, you should group by Year and Month.