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

Enrique Gonzalez R said:

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

Click to expand...

    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

behnam_tr said:

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

B4X:

    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)

Click to expand...