Datetime_Diff BigQuery and Date_Diff Function 101: Syntax & Usage Simplified

Enterprises across the globe are continuously putting efforts into growing their business and once it is booming, data starts generating at a staggering rate. There is a gradual shift towards Cloud-Based Data Warehouses has been observed in the market. Google BigQuery is one such Cloud platform offering On-demand Storage and Computational Resources for high-end query performance. Users can easily query complex data using pre-built functions such as Datetime_Diff BigQuery.

Standard Structured Query Language(SQL), an immensely popular tool among Data Analysts, is used in BigQuery to query your data efficiently. Date_Diff and Datetime_Diff BigQuery SQL functions are some of the most important functions when managing date and time format data.

In this article, you will learn how to effectively use the Date_Diff and Datetime_Diff BigQuery SQL functions for your business.

Table of Contents

• Introduction to Google BigQuery
  • Key Features of Google BigQuery
• Date_Diff BigQuery Function: Syntax and Examples
  • Date_Diff BigQuery Syntax
  • Date_Diff BigQuery Examples
• Datetime_Diff BigQuery Function: Syntax and Examples
  • Datetime_Diff BigQuery Syntax
  • Datetime_Diff BigQuery Examples
• Conclusion

Introduction to Google BigQuery

BigQuery is a Robust Cloud Data Warehouse and Analytics Platform offered by Google. It is a serverless system that doesn’t require you to install any software or maintain and manage huge infrastructure. This is quite a cost-effective solution for a growing business as you don’t need to invest in hardware and the space for large server rooms similar to the conventional On-premise databases.

On-demand petabyte scaling and query results in real-time at a lightning speed ensure a fluid workflow. While designing the architecture of BigQuery, developers at Google made sure to separate the storage and computation resources so that they can be scaled independently without sacrificing performance.

SQL-Based querying allows Data analysts and Business Teams to quickly get On-Board and execute queries to get deeper insights for their firm in real-time.  

Key Features of Google BigQuery

Google has continuously improved BigQuery over the years and has introduced several intuitive features to boost performance and reliability. Some of the most eye-catching features are:

• Seamless Scaling: Computational Resources are automatically scaled according to the workload instantly as you don’t need to manually resize the cluster. It can comfortably scale storage to petabytes on-demand. This is a completely managed service as BigQuery takes care of patching, upgrades, compute, and storage resource sizing. 
• Ease-to-Follow: In just a few minutes and click you can set up your Data Warehouse with simple and clear instructions at every step. You don’t need to size or deploy the clusters, configure keys or indexes, or install any software. The straightforward process allows you to upload your data and start running SQL Queries in no time.
• Real-Time Analytics: Data Analysts get real-time query results allowing them to prepare and generate accurate reports on the fly. Unlike the Traditional Data Warehouses that are limited by their hardware, here you can use thousands of core per query.
  BigQuery allows the streaming of millions of rows of data every minute and analyse terabytes of data in seconds helping the business teams make data-driven decisions.
• Google Ecosystem: Being part of the Google environment, you can leverage the data transfer services among the products provided by Google such as Google Marketing Platform, Google Ads, YouTube, Teradata, etc. For analysing your data, you can also connect to Google Sheets or use the integration with Google Data Studio (Business Intelligence Tool).
• Secure: Based on Colossus, Google’s Global Storage System, BigQuery assures its users of No loss of Data by timely backup, data replication, and smart recovery strategies. By default, data is stored across multiple data centers for maximum data durability and service uptime.
  With Admin privileges, you can set access permissions to your datasets and projects for both groups and individuals. Also, data is encrypted at all times, whether at rest or in transit.
• Flexible Pricing: Bigquery charges only for the resources that you use. With the $0.02 per GB per month for storage, $0.01 per 200MB for streaming inserts, and free first 1 TB of query data processed per month, BigQuery is an affordable choice for managing and processing your data. 
• Machine Learning Integration: In-built BigQuery Machine Learning Capabilities allow you to directly evaluate the ML models through simple SQL commands without requiring the technical knowledge of Machine learning. This eliminates the need of building a separate ML solution or exporting it to an external tool.

Prerequisites

• An active BigQuery account.
• Basic Knowledge of SQL.

Date_Diff BigQuery Function: Syntax and Examples

The Date_Diff BigQuery function provides you the difference between 2 Date type objects according to the specified date_part.

A) Date_Diff BigQuery Syntax

DATE_DIFF(date_expression_a, date_expression_b, date_part)

The above syntax contains the following parameters:

• Date_expression: a and b date_expressions denote the 2 Date Objects.
• Date_part: Represents the range/interval according to which the difference between the 2 dates will be depicted.

Date_diff function supports the following types of date_part:

• DAY:
• WEEK: This date part begins on Sunday.
• WEEK(<WEEKDAY>): Begins on WEEKDAY with valid values for WEEKDAY being SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY.
• ISOWEEK: Uses ISO 8601 week boundaries. ISO weeks begin on Monday.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: Uses the ISO 8601 week-numbering year boundary. The ISO year boundary is the Monday of the first week whose Thursday belongs to the corresponding Gregorian calendar year.

B) Date_Diff BigQuery Examples

Let’s understand its application by the following examples:

• Using DAY as date_part

SELECT DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', DAY) AS days_diff;

Output: 

+-----------+
| days_diff |
+-----------+
| 559       |
+-----------+

The above example displays the number of days between the two dates with days_diff as the alias(Column Heading for the Output)

• Using YEAR, ISOYEAR as date_part

SELECT DATE_DIFF('2017-12-30', '2014-12-30', YEAR) AS year_diff,
DATE_DIFF('2017-12-30', '2014-12-30', ISOYEAR) AS isoyear_diff;

Output: 

+-----------+--------------+
| year_diff | isoyear_diff |
+-----------+--------------+
| 3         | 2            |
+-----------+--------------+

In the above example, the ISOYEAR shows 2 years difference as the date “2014-12-30” belongs to the 2015 ISO year. This happens because, In ISO, the year starts with the first Monday of the week i.e. 2014-12-29.

• Using CAST for Data type conversion

It is often possible that there is a timestamp included within the date. You can add the CAST(datetime_col as DATE) in your function to get the desired difference between dates.

SELECT DATE_DIFF(CAST('2021-01-05 10:44:22' as DATE),'2021-01-01', DAY) days_diff

Output: 

+-----------+
| days_diff |
+-----------+
| 4     |
+-----------+

• Negative Integer Value

You will get a negative value in the output if the first date in the function is earlier than the second one.

SELECT DATE_DIFF(CAST('2021-03-01' AS DATE), CAST('2021-04-01' AS DATE), WEEK) AS weeks_diff

Output:

+-----------+
| weeks_diff |
+-----------+
| -4    |
+-----------+

Datetime_Diff BigQuery Function:  Syntax and Examples

The Datetime_Diff BigQuery function returns a signed 64-bit integer value that represents the difference between 2 datetime objects in terms of the specified part interval. When executing the Datetime_Diff BigQuery function it is possible that an error may pop out when the difference between the two datetime objects is quite small resulting in overflowing the INT64.

A) Datetime_Diff BigQuery Syntax

DATETIME_DIFF(datetime_expression_a, datetime_expression_b, part)

The above datetime_diff BigQuery syntax contains the following parameters:

• datetime_expression: a and b datetime_expressions denote the 2 datetime objects.
• part: Represents the date or time range/interval according to which the difference between the 2 datetime objects will be depicted.

The datetime_diff BigQuery function supports the following part types:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK: This date part begins on Sunday.
• WEEK(<WEEKDAY>): Begins on WEEKDAY with Valid values for WEEKDAY being SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY.
• ISOWEEK: Uses ISO 8601 week boundaries. ISO weeks begin on Monday.
• MONTH
• QUARTER
• YEAR
• ISOYEAR: Uses the ISO 8601 week-numbering year boundary. The ISO year boundary is the Monday of the first week whose Thursday belongs to the corresponding Gregorian calendar year.

B) Datetime_Diff BigQuery Examples

Let’s dive into the Datetime_diff BigQuery function through the following examples:

• Using DAY in datetime_diff BigQuery function

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;

Output:

+----------------------------+------------------------+--------------------+
| first_datetime             | second_datetime        | difference         |
+----------------------------+------------------------+--------------------+
| 2010-07-07T10:20:00        | 2008-12-25T15:30:00    | 559                |
+----------------------------+------------------------+--------------------+

• Using WEEK and ISO WEEK in datetime_diff BigQuery function

SELECT
  DATETIME_DIFF('2017-12-18', '2017-12-17', WEEK) AS week_diff,
  DATETIME_DIFF('2017-12-18', '2017-12-17', WEEK(MONDAY)) AS week_weekday_diff,
  DATETIME_DIFF('2017-12-18', '2017-12-17', ISOWEEK) AS isoweek_diff;

Output:

+-----------+-------------------+--------------+
| week_diff | week_weekday_diff | isoweek_diff |
+-----------+-------------------+--------------+
| 0         | 1                 | 1            |
+-----------+-------------------+--------------+

In the above Datetime_Diff BigQuery Function example, the part WEEK shows week difference 0 as by default the week starts on Sunday whereas WEEK(MONDAY) means the week starts from Monday i.e. “2017-12-18”. ISO Week also returns 1 as ISO week begins from Monday.

• Using CAST for data type conversion in datetime_diff BigQuery function

For a correct result, the data type for both objects should be datetime. You can ensure that by adding CAST(datetime_col as DATETIME) to your datetime_diff BigQuery function:

SELECT DATETIME_DIFF(CAST('2021-01-01' as DATETIME),'2021-01-05 03:04:00', DAY) days_diff

Output: 

+-----------+
| days_diff |
+-----------+
| 4         |
+-----------+

• Negative Integer Output in datetime_diff BigQuery function

A negative value will appear in the output if the first datetime in the datetime_diff BigQuery function is earlier than the second one.

SELECT DATETIME_DIFF(2021-03-01 04:04:00’, '2021-04-01 05:04:00' , WEEK) AS weeks_diff

Output:

+-----------+
| weeks_diff |
+-----------+
| -4    |
+-----------+

For more examples and tips on the datetime_diff BigQuery function, you can visit the BigQuery Documentation.

Conclusion

In this article, you have learned how to effectively use the date_diff and datetime_diff BigQuery functions. Data Analysts can easily get started with these functions using standard SQL in BigQuery. Google BigQuery automatically allocates the optimal amount of computing resources for your Queries.

Owing to its Massively Parallel Processing, users from various departments across your enterprise can execute multiple complex queries simultaneously. The accelerated analytics and the easy-to-understand User Interface have made BigQuery immensely popular among several organizations.

Once your Products and Services kicks off in the market and your customer base increases, data is generated at an exponential rate across all your applications in your firm. Handling and transferring this data regularly to your BigQuery Data Warehouse can be a cumbersome task.

You would be required to devote a section of your engineering bandwidth to Integrate, Clean, Transform and Load your data to BigQuery for further Business Analysis. All of this can be comfortably automated by a Cloud-Based ETL Tool like Hevo Data.

Visit our Website to Explore Hevo

Hevo Data, a No-Code Data Pipeline can efficiently transfer your data from a vast sea of sources to a Data Warehouse like Google BigQuery or a destination of your choice. It is a completely automated, reliable, and secure service that doesn’t require you to write any code!

If you are using Google BigQuery as Data Warehousing and Analytics solution for your business and searching for a stress-free alternative to Manual Data Integration, then Hevo can seamlessly automate this for you. Hevo, with its strong integration with 100+ sources & BI tools, allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiffy.

Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process. Check out the pricing details to easily understand which plan meets all your business needs.

Share with us your experience of using Date_diff and Datetime_diff BigQuery functions. Let us know in the comments section below!