Generating a range of dates in MySQL

2021-01-23 (Saturday) | 800 words (~4 minutes reading)

It’s often useful to have a range of contiguous dates when building SQL queries, for example by using a left join to ensure that data from other tables that might have missing dates will get a null value in those rows instead of skipping the rows entirely.

Postgres has its wonderful generate_series function which can solve a lot of these problems. Unfortunately MySQL doesn’t have the same function.

You can generate a range of contiguous dates in MySQL with a query like this:

SELECT date_series.dates
    FROM (
        SELECT curdate() - INTERVAL (units.mul + (10 * tens.mul) + (100 * hundreds.mul) + (1000 * thousands.mul)) DAY AS dates
        FROM       (SELECT 0 AS mul 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) AS units
        CROSS JOIN (SELECT 0 AS mul 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) AS tens
        CROSS JOIN (SELECT 0 AS mul 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) AS hundreds
        CROSS JOIN (SELECT 0 AS mul 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) AS thousands
    ) date_series

That will produce 10000 dates like this:

+----------+
|dates     |
+----------+
|2021-08-23|
|2021-08-22|
|2021-08-21|
|2021-08-20|
|2021-08-19|
|2021-08-18|
|2021-08-17|
|2021-08-16|
|2021-08-15|
|2021-08-14|
|   ...    |
+----------+

This looks a little crazy at first, but it’s reasonable once you take a look through it. It uses a series of cross joins to build up decimal positions with values from 0 through to 9, for the units, tens, hundreds and thousands positions. It then subtracts each resulting number as a number of days from the current date to produce the range of contiguous dates that we want.

This query uses curdate() - INTERVAL to generate dates going back in time from today. You can swap curdate() for another date to have a different starting point, or do + INTERVAL to generate dates going forwards from the initial date.

This query produces a range of 10000 dates, which is about 27 years and probably sufficient for most purposes. You can adjust the range with a WHERE clause to suit different needs, e.g.:

SELECT a.dates
    FROM (
        SELECT curdate() - INTERVAL (units.mul + (10 * tens.mul) + (100 * hundreds.mul) + (1000 * thousands.mul)) DAY AS dates
        FROM       (SELECT 0 AS mul 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) AS units
        CROSS JOIN (SELECT 0 AS mul 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) AS tens
        CROSS JOIN (SELECT 0 AS mul 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) AS hundreds
        CROSS JOIN (SELECT 0 AS mul 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) AS thousands
    ) a
    WHERE a.dates >= curdate() - INTERVAL 1 YEAR
      AND DAYNAME(a.dates) = 'Monday'

That limits the date range to dates up to a year ago, and only Mondays:

+----------+
|dates     |
+----------+
|2021-08-23|
|2021-08-16|
|2021-08-09|
|2021-08-02|
|2021-07-26|
|2021-07-19|
|2021-07-12|
|2021-07-05|
|2021-06-28|
|2021-06-21|
|   ...    |
+----------+

By the way, if you need help with MySQL or SQL in your business, you can hire me as a freelance SQL developer.


Tech mentioned