Generate a Series of Dates in SQL (Calendar Table per Dialect)

A row per day (a “calendar” / “date spine”) to left-join against so gaps in your data show as zero.

Native generators

-- PostgreSQL
SELECT d::date
FROM generate_series('2024-01-01'::date, '2024-01-31'::date, INTERVAL '1 day') AS d;

-- DuckDB
SELECT d::date
FROM generate_series(DATE '2024-01-01', DATE '2024-01-31', INTERVAL 1 DAY) AS t(d);

-- BigQuery
SELECT d
FROM UNNEST(GENERATE_DATE_ARRAY('2024-01-01', '2024-01-31', INTERVAL 1 DAY)) AS d;

Recursive CTE (MySQL 8+, SQLite, and portable everywhere)

WITH RECURSIVE dates(d) AS (
  SELECT DATE '2024-01-01'
  UNION ALL
  SELECT d + INTERVAL 1 DAY          -- MySQL/DuckDB; SQLite: DATE(d, '+1 day')
  FROM dates
  WHERE d < DATE '2024-01-31'
)
SELECT d FROM dates;

Snowflake (generator + row count)

SELECT DATEADD(day, SEQ4(), DATE '2024-01-01') AS d
FROM TABLE(GENERATOR(ROWCOUNT => 31));

Gotchas: in the recursive CTE, SQLite needs DATE(d, '+1 day') (not + INTERVAL), and MySQL requires WITH RECURSIVE. Snowflake has no generate_series for dates — the GENERATOR(ROWCOUNT)

SEQ4() pattern is the idiom (compute the row count as the day span + 1). Postgres generate_series returns timestamp when given an interval step — cast to ::date.