Generate a Date Series in MySQL using CTEs

Today I needed to run a query in MySQL that needed a series of dates that was independent of any table data. Something like this:

2018-12-01
2018-12-02
2018-12-03
2018-12-04
2018-12-05

I'm used to Postgres so I naturally reached for generate_series. Unfortunately MySQL does not seem to have that function so I had to do some Googling and throw something together with Common Table Expressions. I figure someone else might need this type of thing so I'll share my solution here.

SET @start_date = '2018-12-01';
SET @end_date = '2018-12-15';

WITH digit AS (
    SELECT 0 AS d UNION ALL
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),

num_seq AS (
    SELECT a.d + (10 * b.d) + (100 * c.d) + (1000 * d.d) AS num
    FROM digit a
        CROSS JOIN
        digit b
        CROSS JOIN
        digit c
        CROSS JOIN
        digit d
    ORDER BY 1
),

dates AS (
    SELECT
        DATE_FORMAT(
            DATE_SUB(current_date, INTERVAL num_seq.num DAY ), '%Y-%m-%d'
        ) AS formatted_date

    FROM num_seq

    WHERE num_seq.num <= datediff(current_date, @start_date)
    AND num_seq.num >= datediff(current_date, @end_date)
)

SELECT formatted_date FROM dates

ORDER BY 1 ASC

This generates the following:

+----------------+
| formatted_date |
+----------------+
| 2018-12-01     |
| 2018-12-02     |
| 2018-12-03     |
| 2018-12-04     |
| 2018-12-05     |
| 2018-12-06     |
| 2018-12-07     |
| 2018-12-08     |
| 2018-12-09     |
| 2018-12-10     |
| 2018-12-11     |
| 2018-12-12     |
| 2018-12-13     |
| 2018-12-14     |
| 2018-12-15     |
+----------------+
15 rows in set (0.00 sec)

That series can now be used to join other tables and sets of data to. The code cannot have an end_date that is in the future as is, but I'm sure that functionality wouldn't be too difficult to add.

comments powered by Disqus