Generating a series of dates [duplicate]

Question

This question already has an answer here:

• generate days from date range 25 answers

What is the best way in mysql to generate a series of dates in a given range?

The application I have in mind is to write a report query that returns a row for every date, regardless of whether there is any data to report. In its simplest form:

select dates.date, sum(sales.amount)
from <series of dates between X and Y> dates
left join sales on date(sales.created) = dates.date
group by 1

I have tried creating a table with lots of dates, but that seems like a poor workaround.

Answer 1

You can also use Temporary Table to generate date series. Check below query:

CREATE TEMPORARY TABLE daterange (dte DATE); 

SET @counter := -1;
WHILE (@counter < DATEDIFF(DATE(_todate), DATE(_fromdate))) DO 
    INSERT daterange VALUES (DATE_ADD(_fromdate, INTERVAL @counter:=@counter + 1 DAY));
END WHILE;

SELECT dates.dte, SUM(sales.amount)
FROM daterange dates
LEFT JOIN sales ON DATE(sales.created) = dates.date
GROUP BY dates.dte;

Answer 2

You may use a variable generate date series:

Set @i:=0;
SELECT DATE(DATE_ADD(X, 
INTERVAL @i:=@i+1 DAY) ) AS datesSeries
FROM yourtable, (SELECT @i:=0) r
where @i < DATEDIFF(now(), date Y) 
;

Not sure if this is what you have tried :) though.

Next use above generated query as a table to left join:

set @i:=0;

select
d.dates,
sum(s.amount) as TotalAmount
from(
SELECT DATE(DATE_ADD(X, 
INTERVAL @i:=@i+1 DAY) ) AS dateSeries
FROM Sales, (SELECT @i:=0) r
where @i < DATEDIFF(now(), date Y) 
) dates d 
left join Sales s
on Date(s.Created) = Date(d.dateSeries)
group by 1
;

Answer 3

if you're in a situation like me where creating temporary tables is prohibited, and setting variables is also not allowed, but you want to generate a list of dates in a specific period, say current year to do some aggregation, use this

select * from 
(select affffdate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where gen_date between '2017-01-01' and '2017-12-31'

Answer 4

I think having a calendar table is a good idea; you can gain a lot of reporting and query functionality, especially when filling sparse data ranges.

I found this article with what seems to be a good example.