Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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, 2011 at 8: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, 2011 at 8: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, 2011 at 8:47
                Extremely hacky, but works like a charm. I'm totally using it. Way better than other methods I saw.
– Alex Weinstein
                Oct 10, 2011 at 0:41
                @Milimetric No. It just outputs 365 days in a row, but you may need limit 366 for leap years.
– Karolis
                Mar 27, 2013 at 18:46
                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, 2013 at 18:55
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
                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, 2022 at 7: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 at 19:33
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
        any_table,
    (select @num:=-1) num
    limit
) as dt
where year(date)=2011
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
        your_table,
    (select @num:=-1) num
    limit
        366 ) as dt
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.