generate_series() equivalent in MySQL

Question

I need to do a query and join with all days of the year but in my db there isn't a calendar table.
After google-ing I found generate_series() in PostgreSQL. Does MySQL have anything similar?

My actual table has something like:

date     qty
1-1-11    3
1-1-11    4
4-1-11    2
6-1-11    5

But my query has to return:

1-1-11    7
2-1-11    0
3-1-11    0
4-1-11    2
and so on ..

It's not the "right" solution to do in app logic. It's better, really better to do via sql (if it is possible). If it will not possible.. ok, i will do in my app logic ... — stighy, Jul 29 '11 at 08:35
@stighly: Well, you can solve half of the problem on MySQL. That is, you can `GROUP BY date` and `SUM(qty) qty`, but I don't recall any solution of the top of my head to add rows for missing sequences. It's better to do it in app logic, if a date has a qty value, show it, else show 0. — Shef, Jul 29 '11 at 08:37
Actually, i'm solving it generating an entire calendar table.. from 1-1-2010 (for instance) to 31-12-2020. It works.. but it's not very elegant ... — stighy, Jul 29 '11 at 08:47

score 28 · Accepted Answer · edited Apr 22 '14 at 17:41

28

This is how I do it. It creates a range of dates from 2011-01-01 to 2011-12-31:

select 
    date_format(
        adddate('2011-1-1', @num:=@num+1), 
        '%Y-%m-%d'
    ) date
from 
    any_table,    
    (select @num:=-1) num
limit 
    365

-- use limit 366 for leap years if you're putting this in production

The only requirement is that the number of rows in any_table should be greater or equal to the size of the needed range (>= 365 rows in this example). You will most likely use this as a subquery of your whole query, so in your case any_table can be one of the tables you use in that query.

edited Apr 22 '14 at 17:41

Yahel

37,023
22
103
153

answered Jul 29 '11 at 09:38

Karolis

9,396
29
38

4

Extremely hacky, but works like a charm. I'm totally using it. Way better than other methods I saw. – Alex Weinstein Oct 10 '11 at 00:41
Doesn't this break during leap years? – Milimetric Mar 19 '13 at 20:48
@Milimetric No. It just outputs 365 days in a row, but you may need `limit 366` for leap years. – Karolis Mar 27 '13 at 18:46
@Milimetric Show your observations as comments in instead of editing the answer. – Clodoaldo Neto Mar 28 '13 at 14:47
11

With all due respect, the answer is wrong and should be edited. This is a wiki for solutions. Someone copying and pasting that code may not have thought of the special case I mention and get burned by it. Therefore the answer should be edited. I don't want to get into a revert war but you should probably add my comment back. – Milimetric Mar 28 '13 at 18:55
Better written this way, to prevent table dependency: `SELECT CURRENT_DATE() - INTERVAL (@num := @num + 1) DAY AS date FROM (SELECT 1), (SELECT @num := -1) AS num LIMIT 365;` – Steven Moseley Jan 16 '17 at 22:18
MySQL variables are deprecated in MySQL 8.x. This answer is becoming highly obsolete. – The Impaler Aug 22 '20 at 19:13
This is so great! Thanks. Combined with https://stackoverflow.com/questions/64135343/generating-counts-of-open-tickets-over-time-given-opened-and-closed-dates you can also easily generate a histogram of open tickets with mysql. thanks! – Fips Apr 16 '22 at 21:33

森林虎猫 · Answer 2 · 2021-12-05T06:36:53.540

8

Just in case someone is looking for generate_series() to generate a series of dates or ints as a temp table in MySQL.

With MySQL8 (MySQL version 8.0.27) you can do something like this to simulate:

WITH RECURSIVE nrows(date) AS (
SELECT MAKEDATE(2021,333) UNION ALL 
SELECT DATE_ADD(date,INTERVAL 1 day) FROM nrows WHERE  date<=CURRENT_DATE
)
SELECT date FROM nrows;

Result:

edited Dec 05 '21 at 06:36

answered Dec 05 '21 at 06:26

森林虎猫

101
1
4

1

The best solution so far, because it does'nt need a real table with enough rows for sure. A little correction maybe, the WHERE-Clause should be `date < CURRENT_DATE` (instead of "date <= CURRENT_DATE"), this way the generated dates stop today, not tomorrow. – Dave_B. Sep 14 '22 at 07:46
I know this is old, but this is a great answer. You can change `MAKEDATE` to `DATE('2023-01-01')` AND the `WHERE` condition in the union to generate your needed date range. Thanks @森林虎猫 – thetaiko Aug 17 '23 at 19:33

score 7 · Answer 3 · edited May 24 '21 at 18:23

7

Enhanced version of solution from @Karolis that ensures it works for any year (including leap years):

select date from (
    select
        date_format(
        adddate('2011-1-1', @num:=@num+1),
        '%Y-%m-%d'
    ) date
    from
        any_table,
    (select @num:=-1) num
    limit
        366
) as dt
where year(date)=2011

edited May 24 '21 at 18:23

eshirvana

23,227
3
22
38

answered Nov 02 '16 at 23:13

davpar

71
1
2

Carmine Tambascia · Answer 4 · 2020-01-15T12:27:33.527

I was looking to this solution but without the "hardcoded" date, and I came-up with this one valid for the current year(helped from this answers). Please note the

where year(date)=2011

is not needed as the select already filter the date. Also this way, it does not matter which table(at least as stated before the table has at least 366 rows) is been used, as date is "calculated" on runtime.

 select date from (
    select
        date_format(
        adddate(MAKEDATE(year(now()),1), @num:=@num+1),
        '%Y-%m-%d'
    ) date
    from
        your_table,
    (select @num:=-1) num
    limit
        366 ) as dt

generate_series() equivalent in MySQL

4 Answers4

Linked

Related