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 ..

Olin

Why can't you do this in your app logic layer? – Gnathic

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 ... – Olin

@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. – Gnathic

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 ... – Olin

Answer 1

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.

Terrel

Extremely hacky, but works like a charm. I'm totally using it. Way better than other methods I saw. – Bear

Doesn't this break during leap years? – Attend

@Attend No. It just outputs 365 days in a row, but you may need limit 366 for leap years. – Terrel

@Attend Show your observations as comments in instead of editing the answer. – Bryan

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. – Attend

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; – Hamner

MySQL variables are deprecated in MySQL 8.x. This answer is becoming highly obsolete. – Fein

This is so great! Thanks. Combined with #64135843 you can also easily generate a histogram of open tickets with mysql. thanks! – Xuanxunit

This solution only returns however many rows there are in any_table. – Saker

Answer 2

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:

2021-11-29
2021-11-30
2021-12-01
2021-12-02
2021-12-03
2021-12-04
2021-12-05
2021-12-06

Protoxide

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. – Herrera

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 @Protoxide – Maidenhair

Answer 3

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

Forlorn

Answer 4

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

Screak