Google BigQuery offers its users many features to make data analysis easier. It comes with the SQL language support that allows business users to access data easily. BigQuery also offers many out-of-the-box functions, such as: Datetime_Diff that allows users to easily query complex data. In this blog post, you will learn how to use the Datetime_Diff BigQuery function with some examples.
What is the Datetime_Diff function?
Google BigQuery’s Datetime_Diff function allows us to easily calculate the difference between the 2 DateTime objects. Google BigQuery uses SQL language to access and manipulate data stored in the data warehouse. The BigQuery Datetime_Diff function is useful when the data has more than one column related to DateTime objects, such as calculating the number of days between the order date and the ship date.
The Datetime_Diff function returns the number calculated from the difference between 2 DateTime objects, and the intervals are defined by the specified part, such as day, hour, seconds, and so on. The syntax for the BigQuery SQL Datetime_Diff function is shown below:
DATETIME_DIFF(date_expression_a, date_expression_b, part)
In the above syntax, if the first date expression comes before the second date expression or the date_expression_a is less than date_expression_b, then the return value is negative or zero. Also, the BigQuery SQL query Datetime_Diff can throw the error when the difference between the 2 Datetime objects is quite small, resulting in an overflow of INT64 where the.
The following component value data types are supported by Datetime_Diff BigQuery SQL function are given below.
- WEEK: The WEEK date portion starts on SUNDAY.
): This WEEK date portion starts on WEEKDAY and the valid values for the WEEKDAY date portion are SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY.
- ISOWEEK: This part of the Datetime_Diff follows ISO 8601 week limits starting on Monday.
- MONTH: This part of the Datetime_Diff BigQuery function is used except when the first two arguments of the date expressions are TIMESTAMP objects.
- ISOYEAR: It follows the ISO 8601 week-numbering year border. The ISOYEAR boundary is the MONDAY of the first week, and also whose THURSDAY belongs to the corresponding Gregorian calendar year.
Example of Datetime_Diff BigQuery SQL Function
Now let’s go through some examples to understand how to use the Datetime_Diff function in Google BigQuery. The following examples are given below.
This example uses DAY as part of the Datetime_Diff function. The following query in Google BigQuery is given below:
DATETIME “2010-07-07 10:20:00” as first_datetime,
DATETIME “2008-12-25 15:30:00” as second_datetime,
DATETIME_DIFF(DATETIME “2010-07-07 10:20:00”,
DATETIME “2008-12-25 15:30:00”, DAY) as difference;
† first_datetime | second_datetime | difference |
† 2010-07-07T10:20:00 | 2008-12-25T15:30:00 | 559 |
In the above example, “2010-07-07 10:20:00” is defined as first_datetime and “2008-12-25 15:30:00” is defined as second_datetime that occurred before the first date expression, meaning the expected output will be a positive integer. When the query is run, it calculates the number of days between both DateTime expressions because the date portion DAY is specified.
In this example, the 2 queries are executed with DAY and WEEK as the sub-interval. The following Datetime_Diff BiqQuery SQL query is given below.
DATETIME_DIFF(DATETIME ‘2017-10-15 00:00:00’,
DATETIME ‘2017-10-14 00:00:00’, DAY) as days_diff,
DATETIME_DIFF(DATETIME ‘2017-10-15 00:00:00’,
DATETIME ‘2017-10-14 00:00:00’, WEEK) as weeks_diff;
† days_diff | weeks_diff |
† 1 | 1 |
The 2 DateTime objects are 24 hours apart, so the first Datetime_Diff function query returns 1 day. The second Datetime_Diff query containing the WEEK component returns 1 because the function counts the number of component boundaries in the range of the specified DateTime.
Like every WEEK in the Datetime_Diff starts from SUNDAY, so when the Datetime_Diff BiqQuery function calculates the WEEK between both 2017-10-14 00:00:00 and 2017-10-15 00:00:00. It counts as a partial boundary between SATURDAY and SUNDAY.
This example of the BigQuery function Datetime_Diff uses YEAR and ISOYEAR as the division interval. The next question is given below.
‘2014-12-30 00:00:00’, YEAR) AS year_diff,
‘2014-12-30 00:00:00’, ISOYEAR) AS isoyear_diff;
† year_diff | isoyear_diff |
† 3 | 2 |
In the example above, the first query of Datetime_Diff uses YEAR as a date part. It returns 3 as a year difference between ‘2017-12-30 00:00:00’ and ‘2014-12-30 00:00:00’ because it counts the number of Gregorian calendar year boundaries between both given date expressions .
The second query that uses ISOYEAR as a date part in the Datetime_diff function returns 2 because the second date expression is ‘2014-12-30 00:00:00’
belongs to the ISO year 2015. The first THURSDAY of the year 2015 was 01-01-2015 and the ISO year 2015 starts on the preceding Monday, 2014-12-29.
In this blog post, you will learn about the Datetime_Diff BigQuery function that allows users to easily calculate the difference between the 2 date expressions in terms of specified partial intervals. You also went through some examples of how to use the Datetime_Diff function in SQL queries in Google BigQuery. Google BigQuery is a widely used Cloud Data Warehouse service by companies to store and analyze their data.