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