Generating a series of dates

Question

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.

score 40 · Answer 1 · answered Sep 01 '17 at 01:41

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 adddate('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'

score 24 · Accepted Answer · answered Dec 31 '12 at 18:43

24

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.

answered Dec 31 '12 at 18:43

BellevueBob

9,498
5
29
56

This is a great idea. Thanks for the article link. – Alagu Feb 24 '20 at 16:34
@BellevueBob The website is down with the error: PDOException: SQLSTATE[HY000] [2003] Can't connect to MySQL server on '127.0.0.1' (111) in lock_may_be_available() (line 167 of /var/www/www.brianshowalter.com/includes/lock.inc). – ptyshevs Apr 27 '21 at 06:30

bonCodigo · Answer 3 · 2012-12-31T18:50:05.780

8

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
;

edited Dec 31 '12 at 18:50

answered Dec 31 '12 at 18:40

bonCodigo

14,268
1
48
91

@Bohemian that's a fast fish to catch ;) I too need one more hat :D – bonCodigo Jan 03 '13 at 19:39
Which hat are you trying to get? – Bohemian Jan 03 '13 at 19:43
@Bohemian **Gangnam Style** and **Where is he?** You are welcome for any of my awesome questions or awesome answers ;) blimey! hahaha but it's *lamer* to wait for another year :P – bonCodigo Jan 03 '13 at 19:51
OK - You got one, and close to the other... good luck – Bohemian Jan 03 '13 at 19:57
@Bohemian muchas gracias! :D whee!! – bonCodigo Jan 03 '13 at 19:59
Actually, you could help me get a gold badge by voting for the other guy's accepted answer [here](http://stackoverflow.com/questions/7281469/7285304#7285304) – Bohemian Jan 05 '13 at 00:17
@Bohemian happy to vote up good answers and questions. You want me to vote for the other guy? Sorry brain freeze, so accepted answer to be upvoted right? ;) – bonCodigo Jan 05 '13 at 00:26
Actually, vote for the other guy too – Bohemian Jan 05 '13 at 01:21
1

It should be noted that `yourtable` must have more records that the desired time series but preferable not much more or performance will suffer. – andig Oct 29 '13 at 16:02
In this example, `Sales` must have more records than you want unique dates :) Clever though!! – rjh Jun 26 '17 at 22:21

Saharsh Shah · Answer 4 · 2013-01-02T12:57:05.910

5

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;

edited Jan 02 '13 at 12:57

answered Jan 01 '13 at 12:38

Saharsh Shah

28,687
8
48
83

4

As this question is MySQL tagged only this is not MySQL valid code, this is SQL Server (MSSQL) – Raymond Nijland Jul 18 '19 at 15:33

Generating a series of dates

4 Answers4

Linked

Related