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