generate_series() equivalent in MySQL

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

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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:<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="d9e499b7acb4">[email protected]</a>+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.

Method 2

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:<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="d5e895bba0b8">[email protected]</a>+1),
        '%Y-%m-%d'
    ) date
    from
        any_table,
    (select @num:=-1) num
    limit
        366
) as dt
where year(date)=2011

Method 3

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:<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="211c614f544c">[email protected]</a>+1),
        '%Y-%m-%d'
    ) date
    from
        your_table,
    (select @num:=-1) num
    limit
        366 ) as dt

Method 4

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:

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating