[sqlite] Summing values by date, returning 0 for missing dates

[R Smith]

I should add...

The initial CTE setting up minDT and maxDT can be ANY dates, it doesn't 
have to come from the MIN/MAX in my_table, for example:


WITH dtRange(minDT,maxDT) AS (
     SELECT '2015-12-30 00:00:00', datetime(date('now','localtime','-3 
months'))
     -- 30 December 2015 to today 3 months ago, which is 12th Feb 2016 
for me
), dtAll(dt) AS (
     SELECT minDT FROM dtRange
   UNION ALL
     SELECT datetime(dt,'+1 day') FROM dtAll,dtRange WHERE dt < maxDT
), my_full(time_date, num) AS (
     SELECT time_date, num FROM my_table
   UNION ALL
     SELECT DT, 0 FROM dtAll
)
SELECT date(time_date) as time_date, sum(num) AS sum_num
   FROM my_full
  GROUP BY time_date
  ORDER BY time_date ASC
;


   -- time_date    | sum_num
   -- ------------ | -------
   -- 2015-12-30   |    0
   -- 2015-12-31   |    0
   -- 2016-01-01   |    4
   -- 2016-01-02   |    0
   -- 2016-01-03   |    2
   -- 2016-01-04   |    0
   -- 2016-01-05   |    0
   -- 2016-01-06   |    0
   -- 2016-01-07   |    0
   -- 2016-01-08   |    0
   -- 2016-01-09   |    0
   -- 2016-01-10   |    0
   -- 2016-01-11   |    0
   -- 2016-01-12   |    0
   -- 2016-01-13   |    0
   -- 2016-01-14   |    0
   -- 2016-01-15   |    0
   -- 2016-01-16   |    0
   -- 2016-01-17   |    0
   -- 2016-01-18   |    0
   -- 2016-01-19   |    0
   -- 2016-01-20   |    0
   -- 2016-01-21   |    0
   -- 2016-01-22   |    0
   -- 2016-01-23   |    0
   -- 2016-01-24   |    0
   -- 2016-01-25   |    0
   -- 2016-01-26   |    0
   -- 2016-01-27   |    0
   -- 2016-01-28   |    0
   -- 2016-01-29   |    0
   -- 2016-01-30   |    0
   -- 2016-01-31   |    0
   -- 2016-02-01   |    0
   -- 2016-02-02   |    0
   -- 2016-02-03   |    0
   -- 2016-02-04   |    0
   -- 2016-02-05   |    0
   -- 2016-02-06   |    0
   -- 2016-02-07   |    0
   -- 2016-02-08   |    0
   -- 2016-02-09   |    0
   -- 2016-02-10   |    0
   -- 2016-02-11   |    0
   -- 2016-02-12   |    0


Cheers,
Ryan