Generate dates between two dates (date spine)

-- Postgres / DuckDB.
SELECT * FROM generate_series(DATE '2026-01-01', DATE '2026-01-31', INTERVAL '1 day');

-- BigQuery.
SELECT * FROM UNNEST(
  GENERATE_DATE_ARRAY(DATE '2026-01-01', DATE '2026-01-31', INTERVAL 1 DAY)
) AS day;

-- Snowflake.
SELECT DATEADD(day, seq4(), DATE '2026-01-01') AS day
FROM   TABLE(GENERATOR(ROWCOUNT => 31));

-- Recursive CTE — portable.
WITH RECURSIVE date_spine(day) AS (
  SELECT DATE '2026-01-01'
  UNION ALL
  SELECT day + INTERVAL '1 day' FROM date_spine WHERE day < DATE '2026-01-31'
)
SELECT * FROM date_spine;

-- MySQL 8+.
WITH RECURSIVE date_spine AS (
  SELECT DATE '2026-01-01' AS day
  UNION ALL
  SELECT DATE_ADD(day, INTERVAL 1 DAY) FROM date_spine WHERE day < DATE '2026-01-31'
)
SELECT * FROM date_spine;

-- Last 90 days, ending today.
SELECT day::DATE AS day
FROM   generate_series(
  CURRENT_DATE::TIMESTAMP - INTERVAL '89 days',
  CURRENT_DATE::TIMESTAMP,
  INTERVAL '1 day'
) AS t(day);

-- Weekly spine for the past year (one row per week start).
SELECT day::DATE AS day
FROM   generate_series(
  DATE_TRUNC('week', CURRENT_DATE::TIMESTAMP - INTERVAL '52 weeks'),
  CURRENT_DATE::TIMESTAMP,
  INTERVAL '1 week'
) AS t(day);

-- Every first-of-month for two years.
SELECT day::DATE AS day
FROM   generate_series(
  DATE '2025-01-01',
  DATE '2026-12-01',
  INTERVAL '1 month'
) AS t(day);

-- Cross-product spine (every day × every region) for a regional time-series.
SELECT s.day, r.region
FROM   generate_series(DATE '2026-01-01', DATE '2026-12-31', INTERVAL '1 day') AS s(day)
CROSS JOIN (VALUES ('EU'), ('US'), ('AP')) AS r(region);

-- Find days with no events.
SELECT s.day::DATE AS day
FROM   generate_series(DATE '2024-02-01', DATE '2024-02-29', INTERVAL '1 day') AS s(day)
LEFT JOIN ecom_events e ON e.event_timestamp::DATE = s.day::DATE
WHERE  e.event_id IS NULL;