SQL
Generating a Series of Dates or Numbers in SQL
Create a sequence of dates, times, or integers programmatically in SQL, essential for time-series analysis, filling gaps in data, or calendar generation.
-- For PostgreSQL / SQLite (using GENERATE_SERIES)
SELECT generate_series('2023-01-01'::date, '2023-01-31'::date, '1 day'::interval)::date AS day_series;
-- For MySQL (using Recursive CTE)
WITH RECURSIVE DateSeries AS (
SELECT CAST('2023-01-01' AS DATE) AS day_series
UNION ALL
SELECT DATE_ADD(day_series, INTERVAL 1 DAY)
FROM DateSeries
WHERE day_series < '2023-01-31'
)
SELECT day_series FROM DateSeries;
-- For SQL Server (using Recursive CTE)
WITH DateSeries AS (
SELECT CAST('2023-01-01' AS DATE) AS day_series
UNION ALL
SELECT DATEADD(day, 1, day_series)
FROM DateSeries
WHERE day_series < '2023-01-31'
)
SELECT day_series FROM DateSeries
OPTION (MAXRECURSION 366); -- Increase if generating more than 365 days, or use a Tally Table approach for very large series
How it works: This snippet demonstrates how to generate a sequence of dates or numbers programmatically in SQL. Different database systems have different preferred methods: `GENERATE_SERIES` for PostgreSQL/SQLite, and `WITH RECURSIVE` CTE for MySQL and SQL Server. This technique is invaluable for scenarios like creating calendar tables, filling gaps in time-series data, or generating test data for reports.