BigQuery Basics: Generating Date and Datetime Lists with BigQuery SQL

In over 6 years working in data, there have been a couple of times when having a simple way of generating a table of dates/timestamps would have saved me a LOT of time. A couple of times in 6 years doesn’t sound too bad, but those couple of times were memorable enough that I was in awe when first using BigQuery and discovering GENERATE_DATE_ARRAY.

If you’ve never needed to generate dates before or you’ve long worked with an RDBMS that can do so easily…you are lucky!

(Note: For generate date/dateime lists with MySQL, check out this post)

Basic Syntax

DATE

GENERATE_DATE_ARRAY(start_date, end_date, [INTERVAL INT64_expr date_part])

• Valid date_part values are: DAY, WEEK, MONTH, QUARTER, YEAR
• If you only enter start_date and end_date, the default interval is 1 day
• Values are calculated based on the start_date (the interval is applied to the start date). For example, using a 1 week interval does not actually give dates starting on Sunday (or Monday for ISO weeks) but calculates start_date + 7 days, then continues adding 7 days (e.g. 2021–01–01, 2021–01–08, 2021–01–15, …)
• As hinted by the function name, the output is an…