Sqlite get a list of last 30 days, one row per day

Question

I have the date stored in a int field and when I select it I use

strftime('%Y-%m-%d', table.date/1000, 'unixepoch') as date

I need to have data for the past 30 days for charting purposes, this means that I need to have in my final query something like:

2017-01-18 ,2

2017-01-17, 0

2017-01-16, 0

2017-01-15, 1

In my table I only have rows for 18th and 15th Jan. So running a query against it, will only return rows for the days that exists,

2017-01-18 ,2

2017-01-15, 1

However, I need to have rows, with 0 value, for the past 30 days that don't have any values, as a final result. In order to have this, I need a way to union my data with a query that returns

now-1 day, 0

now-2 days ,0

now-3 days, 0 ....

for the past 30 days. In this way, when I combine the 2 queries, will results in the desired results.

Using from https://stackoverflow.com/a/32987070/379865

I managed to get the data

  WITH RECURSIVE dates(date) AS (
  VALUES(date('now'))
  UNION ALL
  SELECT date(date, '-1 day')
  FROM dates
  limit 30
)
SELECT date, 0 as value FROM dates;

However I don't know how to union it with my other query since WITH does not seem to go as part of a union all

score 1 · Answer 1 · answered Jan 18 '17 at 17:08

1

I'm not entirely sure what you are after but I'll have a shot.

Aggregate by strftime('%Y-%m-%d', table.date/1000, 'unixepoch') and then have a count.

e.g.

SELECT COUNT(*), strftime('%Y-%m-%d', table.date/1000, 'unixepoch') as date FROM table GROUP BY strftime('%Y-%m-%d', table.date/1000, 'unixepoch')

Hopefully this is what you are looking for ..

answered Jan 18 '17 at 17:08

Ryan

1,863
13
20

Sorry, I did not explained it too well. I've edited the question – Alin Jan 18 '17 at 17:13
I've updated my question again. Now I can get the dates as I want, but not sure how to join with other data – Alin Jan 18 '17 at 17:36

score 0 · Accepted Answer · answered Jan 18 '17 at 18:37

0

WITH does go with compound queries:

WITH ...
SELECT date, 0 AS value FROM dates
UNION ALL
SELECT ...;

However, to remove the dummy rows for dates that exist, you should use a join:

WITH ...
SELECT ...
FROM dates
LEFT JOIN (SELECT ...your actual query...)
USING (date);

answered Jan 18 '17 at 18:37

CL.

173,858
17
217
259

Very interesting. Since I did not really get the LEFT JOIN thing, I had a UNION ALL and then wrapped all in a big select with group by date and sum for value. – Alin Jan 19 '17 at 09:31

Sqlite get a list of last 30 days, one row per day

2 Answers2