stighy stighy - 1 month ago 28
SQL Question

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

Answer

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.

Comments