How to Use GENERATE_DATE_ARRAY in BigQuery
Last updated August 30, 2026 · By the SaturnSQL team
GENERATE_DATE_ARRAY(start, end, INTERVAL n unit) returns an array of dates; UNNEST it to get one row per day. That "date spine" is how you make gappy event data report zero on the days nothing happened.
SELECT day
FROM UNNEST(GENERATE_DATE_ARRAY('2026-01-01', '2026-01-31')) AS day;Filling gaps with a date spine
A GROUP BY on event data only produces rows for days that had events, so charts silently skip quiet days. LEFT JOIN the spine to the aggregate and every calendar day gets a row, with COALESCE turning the missing ones into zeros.
WITH spine AS (
SELECT day
FROM UNNEST(GENERATE_DATE_ARRAY(DATE '2026-01-01', CURRENT_DATE())) AS day
),
daily AS (
SELECT DATE(created_at) AS day, COUNT(*) AS signups
FROM accounts
GROUP BY day
)
SELECT spine.day, COALESCE(daily.signups, 0) AS signups
FROM spine
LEFT JOIN daily USING (day)
ORDER BY spine.day;Steps other than one day
SELECT week_start
FROM UNNEST(
GENERATE_DATE_ARRAY(DATE '2026-01-05', DATE '2026-03-30', INTERVAL 1 WEEK)
) AS week_start;Timestamps and plain numbers
GENERATE_TIMESTAMP_ARRAY does the same for TIMESTAMP values (hourly spines, for example), and GENERATE_ARRAY covers plain numeric sequences.
SELECT ts
FROM UNNEST(
GENERATE_TIMESTAMP_ARRAY(
TIMESTAMP '2026-08-30 00:00:00',
TIMESTAMP '2026-08-30 23:00:00',
INTERVAL 1 HOUR
)
) AS ts;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