Not able to group by year-month in sqlite with date as milliseconddd

1
votes

I have date column value as milliseconds in my sqlite table. Now - I need to group by month, year. For example, For 2020-Jan, sum = 1200, For 2020-Feb, sum = 1500 etc.

I checked couple of existing SO suggestions but strftime is not working for me. (May be because I have stored date as milliseconds instead of other standard format suggested by SQLITE doc.

I tried following code, but returns only one row for me.

select SUM(amount) as amount, 
strftime("%m-%Y", date) as 'month-year' 
from sys_item group by strftime("%m-%Y", date);

Can someone please help on this ?

Here is the sample data

enter image description here

I am expecting two columns result as follow:

2020-08 SUM1 2020-07 SUM2

1
sqliteandroid-sqlite

1 Answers

2
votes

Divide your date by 1000 to strip out the milliseconds and convert it to date:

select strftime('%Y-%m', date/1000, 'unixepoch') [year-month],
       sum(amount) amount  
from sys_item 
group by [year-month];