How to Generate a Date Series in Redshift
Last updated July 25, 2026 · By the SaturnSQL team
generate_series() runs only on the leader node in Redshift, so it fails as soon as you join it to a table. Use a recursive CTE or a numbers table built from an existing table to generate dates you can join against.
Recursive CTE
WITH RECURSIVE dates (day) AS (
SELECT '2026-07-01'::date
UNION ALL
SELECT (day + 1)::date FROM dates WHERE day < '2026-07-31'
)
SELECT day FROM dates;Zero-filled daily counts
WITH RECURSIVE dates (day) AS (
SELECT DATEADD(day, -29, CURRENT_DATE)::date
UNION ALL
SELECT (day + 1)::date FROM dates WHERE day < CURRENT_DATE
)
SELECT d.day, COUNT(o.id) AS orders
FROM dates d
LEFT JOIN orders o ON o.created_at::date = d.day
GROUP BY d.day
ORDER BY d.day;The alternative is a permanent numbers or calendar table, e.g. built once with ROW_NUMBER() over any large table, which is faster to reuse across queries than recomputing the CTE.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free