SQL
Generate a Series of Dates or Numbers
Programmatically create sequences of dates or numbers, useful for gap analysis, time series data generation, or building calendar tables in SQL.
-- PostgreSQL: Generate a series of numbers
SELECT generate_series(1, 10) AS number_sequence;
-- PostgreSQL: Generate a series of dates
SELECT generate_series('2023-01-01'::date, '2023-01-31'::date, '1 day'::interval) AS date_sequence;
-- MySQL 8+: Generate a series of numbers (using recursive CTE)
WITH RECURSIVE NumberSeries (n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM NumberSeries WHERE n < 10
)
SELECT n AS number_sequence FROM NumberSeries;
-- MySQL 8+: Generate a series of dates (using recursive CTE)
WITH RECURSIVE DateSeries (d) AS (
SELECT CAST('2023-01-01' AS DATE)
UNION ALL
SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM DateSeries WHERE d < '2023-01-31'
)
SELECT d AS date_sequence FROM DateSeries;
How it works: This snippet shows how to generate sequences of numbers or dates, which is highly useful for filling gaps in data, creating calendar tables, or generating test data. PostgreSQL provides the convenient `generate_series()` function, allowing specification of start, end, and step. MySQL 8+ achieves similar functionality using recursive Common Table Expressions (CTEs), where a base case defines the series start, and a recursive part incrementally builds the series until a termination condition is met.