SQL
Generating a Series of Dates or Numbers
Learn how to programmatically generate a continuous sequence of dates or numbers in SQL, useful for time-series analysis, filling data gaps, or creating calendars.
-- Example for PostgreSQL using generate_series:
SELECT generate_series('2023-01-01'::date, '2023-01-31'::date, '1 day'::interval)::date AS day;
-- To generate a series of numbers (PostgreSQL):
-- SELECT generate_series(1, 10, 1) AS num;
-- MySQL 8.0+ equivalent using a recursive CTE for dates:
-- WITH RECURSIVE dates AS (
-- SELECT '2023-01-01' AS dt
-- UNION ALL
-- SELECT dt + INTERVAL 1 DAY FROM dates WHERE dt < '2023-01-31'
-- )
-- SELECT dt FROM dates;
How it works: This snippet demonstrates how to generate a series of dates or numbers programmatically. In PostgreSQL, `generate_series` is a versatile function that creates a set of rows containing a sequence of values, perfect for constructing a calendar table, ensuring all dates in a range are present for reporting (even if no corresponding data exists), or creating a number table on the fly. Other databases like MySQL 8.0+ can achieve similar results using recursive CTEs.