The world’s leading publication for data science, AI, and ML professionals.

Working with Times and Dates in BigQuery

What you have to know

Photo by team voyas on Unsplash
Photo by team voyas on Unsplash

Who is not familiar with this situation? What was that thing again with the correct timestamp format? Just google it and next time it will end so again. Here are some of the use cases, that I often come in touch with and which I wanted to write down, in the hope not to have to google them again.

Current Date Time

Let’s start with a very easy but useful one. Get the current time – you might need it for comparison e.g. within analytics or for technical data transformations:

SELECT CURRENT_DATETIME() as now;

This function supports the optional timezone parameter. See Timezone definitions for instructions on how to set a timezone [1].

Example Current Date - Image by Author
Example Current Date – Image by Author

EXTRACT

Return a value corresponding to the specified part from a provided _datetimeexpression. For example, here I extracted only the year. I often use this logic for constraints in analyses or for data transformations, where I convert date formats and store them in new tables in a different format.

SELECT EXTRACT(YEAR FROM DATETIME(2021, 07, 02, 15, 45, 00)) as year;
Result - Image by Author
Result – Image by Author

DATETIME Difference

Calculates the difference between two dates. If the first DATETIME is before the second, the output is negative [1].

Select DATETIME_DIFF(DATETIME"2021–03–03", DATETIME"2021–04–04", Day)as difference;

This results in -32.

Formatting DATETIMES

Format a DATETIME object according to the specified _formatstring. Similar to the other examples, one likes to use this formatting option in the data transformation process.

SELECT
 FORMAT_DATETIME("%c", DATETIME "2008–12–25 15:30:00")
 AS formatted;

Here, for example with %c, the whole date and time representation are output [2].

Formatted Datetime - Image by Author
Formatted Datetime – Image by Author

PARSE DATETIME

With _PARSEDATETIME you can do the opposite of the example mentioned above. It converts the string representation of a DATETIME object into a DATETIME object.

SELECT PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '2021–03–03 13:55:55') AS datetime;
Parsed Datetime - Image by Author
Parsed Datetime – Image by Author

Always useful when other sources for example display the date as string and you want to display it in Bigquery as standardized DATETIME.

LAST DAY

Returns the last day from a DATETIME expression containing the date. This is often used to return the last day of the month. You have the option to specify the date part for which the last day should be returned.

SELECT LAST_DAY(DATETIME '2008–11–25', MONTH) AS last_day

If this parameter is not used, the default value is MONTH. Especially in the area of evaluations, for example for a financial month statement interesting and often used.

Result - Image by Author
Result – Image by Author

Summary

Since I often find myself googling date formats within BigQuery because I forgot what the correct Sql expression was, I have provided you with my cheatsheet on the subject. These are the most common use cases in my opinion – feel free to let me know if I forgot anything.

Sources and Further Readings

[1] https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions?hl=de

[2]https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions?hl=de#supported_format_elements_for_datetime


Related Articles