Google BigQuery offers many features to its users for making Data Analysis easier. It comes with the SQL language support that allows business users to easily access data. Also, BigQuery offers many pre-built functions such as Datetime_Diff that enable users to query complex data easily. In this blog post, you will learn about using the Datetime_Diff BigQuery function using a few examples.
What is the Datetime_Diff Function?
Datetime_Diff function of Google BigQuery 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 Datetime_Diff BigQuery 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 shipping date.
The Datetime_Diff function returns the number calculated by the difference between 2 DateTime objects, and the intervals are defined by the specified part such as day, hour, seconds, etc. The syntax for the Datetime_Diff BigQuery SQL function is given below:
Syntax
DATETIME_DIFF(date_expression_a, date_expression_b, part)
In the above syntax if the first date expression occurs before the second date expression or the date_expression_a is smaller than date_expression_b then the returned value is negative or zero. Also, the Datetime_Diff BigQuery SQL query may output the error when the difference between the 2 Datetime objects is quite small which results in an overflow of INT64 value.
The following part value data types supported by the Datetime_Diff BigQuery SQL function are given below.
- MICROSECOND
- MILLISECOND
- SECOND
- MINUTE
- HOUR
- DAY
- WEEK: The WEEK date part begins on SUNDAY.
- WEEK(<WEEKDAY>): This WEEK date part begins on WEEKDAY and the valid values for the WEEKDAY date part are SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY.
- ISOWEEK: This part of the Datetime_Diff follows ISO 8601 week boundaries which begin 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.
- QUARTER
- YEAR
- ISOYEAR: It follows the ISO 8601 week-numbering year boundary. 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 a few examples to understand how to use the Datetime_Diff function in Google BigQuery. The following examples are given below.
Example 1:
In this example, DAY is used as a part of the Datetime_Diff function. The following query in Google BigQuery is given below:
SELECT
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 which means the expected output will be a positive integer number. When the query is executed it will calculate the number of days between both the DateTime expressions because the date part DAY is specified.
Example 2:
In this example, the 2 queries will be executed with DAY and WEEK as a part interval. The following Datetime_Diff BiqQuery SQL query is given below.
SELECT
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 from each other, so the first Datetime_Diff function query results in 1 day. The second Datetime_Diff query with the WEEK part results in 1 because the function counts the number of part boundaries in the range of given DateTime.
As every WEEK in the Datetime_Diff starts from SUNDAY, so when the Datetime_Diff BiqQuery function calculates WEEK between both the 2017-10-14 00:00:00 and 2017-10-15 00:00:00. It counts as one part boundary between SATURDAY and SUNDAY.
Example 3:
In this example of the Datetime_Diff BigQuery function, YEAR and ISOYEAR are used as a part interval. The following query is given below.
SELECT
DATETIME_DIFF(‘2017-12-30 00:00:00’,
‘2014-12-30 00:00:00’, YEAR) AS year_diff,
DATETIME_DIFF(‘2017-12-30 00:00:00’,
‘2014-12-30 00:00:00’, ISOYEAR) AS isoyear_diff;
+———–+————–+
| year_diff | isoyear_diff |
+———–+————–+
| 3 | 2 |
+———–+————–+
In the above example, the first query of Datetime_Diff is using 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 is counting the number of Gregorian calendar year boundaries between both the given date expressions.
The second query using ISOYEAR as a date part in Datetime_diff function returns 2 because the second date expression ‘2014-12-30 00:00:00’
belongs to the ISO year 2015. The first THURSDAY of the 2015 year was 2015-01-01, and the 2015 ISO year begins on the preceding Monday, 2014-12-29.
Conclusion
In this blog post, you read about the Datetime_Diff BigQuery function that allows users to easily calculate the difference between the 2 date expressions in terms of specified part intervals. You also went through a few examples on 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.