Google BigQuery SQL Dates and Times Cheat SheetA cheat sheet for common BigQuery date and time expressionsRebecca VickeryBlockedUnblockFollowFollowingApr 12Photo by Sonja Langford on UnsplashI have just started working on a time series forecasting project this morning.
The first step in this project, as with most data science tasks, was to gather the data first.
This meant a relatively simple SQL query in Google BigQuery.
Once again I found myself googling how to extract the year from a timestamp, and scrolling through the documentation to find the correct function and realised that I needed to write this down somewhere.
I am writing this into a blog post so that I have something to refer back to, and hopefully this may also help others.
Please note all expressions are based on Standard SQL.
DatesDate partsList of all date parts that can be used in the expressions listed below:DAYOFWEEK (returns 1-7 Sunday is 1)DAYDAYOFYEAR (0-365)WEEK (week of year 0-53, week begins on Sunday)WEEK(<chosen weekday>) (week of year begins on your chosen day e.
SUNDAY)ISOWEEK (ISO 8601 week number, week begins on Monday)MONTHQUARTER (1-4)YEAR (ISO 8601 year number) Extract a date partEXTRACT(part FROM date_expression)Example: EXTRACT(YEAR FROM 2019-04-01)Output: 2019Construct a date from integersDATE(year, month, day)Example: DATE(2019, 04, 01)Output: 2019-04-01Adding and subtracting from datesDATE_ADD(date_expression, INTERVAL INT64_expr date_part)Example: DATE_ADD('2019-04-01', INTERVAL 1 DAY)Output: 2019-04-02DATE_SUB(date_expression, INTERVAL INT64_expr date_part)Example: DATE_SUB('2019-04-01', INTERVAL 1 DAY)Output: 2019-03-31Example use case – dynamic dates:where my_date between DATE_SUB(current_date, INTERVAL 7 DAY) and DATE_SUB(current_date, INTERVAL 1 DAY)Difference between two datesDATE_DIFF(date_expression, date_expression, date_part)Example: DATE_DIFF(2019-02-02, 2019-02-01, DAY)Output: 1Specify the granularity of a dateDATE_TRUNC(date_expression, date_part)Example: DATE_TRUNC(2019-04-12, WEEK)Output: 2019-04-07TimesTime partsMICROSECONDMILLISECONDSECONDMINUTEHOURConstruct a datetime object from integersDATETIME(year, month, day, hour, minute, second)DATETIME(date_expression, time_expression)DATETIME(timestamp_expression [, timezone])Example: DATETIME(2019, 04, 01, 11, 55, 00)Output: 2019-04-01 11:55:00Add and subtract timesDATETIME_ADD(datetime_expression, INTERVAL INT64_expr part)Example: DATETIME_ADD('2019-04-01 11:55:00', INTERVAL 1 MINUTE)Output: 2019-04-01 11:56:00DATETIME_SUB(datetime_expression, INTERVAL INT64_expr part)Example: DATETIME_SUB('2019-04-01 11:55:00', INTERVAL 1 MINUTE)Output: 2019-04-01 11:54:00Difference between two timesDATETIME_DIFF(datetime_expression, datetime_expression, part)Example: DATETIME_DIFF('2019-04-01 11:56:00', '2019-04-01 11:55:00', MINUTE)Output: 1Specify the granularity of a timeDATETIME_TRUNC(datetime_expression, part)Example: DATETIME_TRUNC('2019-04-01 11:55:00', HOUR)Output: 2019-04-01 11:00:00This is not by any means an exhaustive guide to date and time expressions in BigQuery.
It is only meant as a quick reference to those I use most often.
For a more comprehensive guide please see the Google BigQuery documentation.