Making a Table of Dates (Date List) in MySQL

While not quite as pretty as BigQuery’s solution to making date lists, it is possible to make something similar with MySQL. The caveat being that this particular solution requires using a CTE, so it will only work from MySQL version 8.0…!

Solution

The solution is to use a recursive CTE (common table expression) and union dates.

Basic Syntax

Here’s the basic format:

WITH recursive table_name AS (
  SELECT 'start_date' AS DATE
  UNION ALL
  SELECT DATE + INTERVAL INTERVAL_LENGTH
  FROM date_list
  WHERE DATE < 'end_date'
)

Like with any other MySQL time addition/subtraction function, you can add/subtract various different units of time (month, day, hour, second, etc.)

Basic Example

For example, the following query creates a table date_list with dates from ‘2023–01–01’ until the current date:

WITH recursive date_list AS (
  SELECT '2023-01-01' AS DATE
  UNION ALL
  SELECT DATE + INTERVAL 1 DAY
  FROM date_list
  WHERE DATE <= DATE(NOW())
)

SELECT *
FROM date_list

The output will look something like this: