Generating date intervals in BigQuery

Ever had to generate a date interval in BigQuery?

Take a look at the GENERATE_DATE_ARRAY function.

Needs 3 arguments:
- start_date
- end_date
- interval step (DAY, WEEK, MONTH, QUARTER, YEAR)

Since it generates an ARRAY, we would need to UNNEST it to get one date per row.

If you need something more granular, there is the very similar GENERATE_TIMESTAMP_ARRAY, which can generate in increments between MICROSECOND and DAY.

Friendly reminder to not mix and match DATETIME and TIMESTAMP without properly converting between them beforehand - see my previous post.

SELECT

  valid_date

FROM UNNEST(GENERATE_DATE_ARRAY('2021-01-01', '2021-01-31', INTERVAL 1 DAY)) AS valid_date