BigQuery Date Functions 101: Key Syntax and Usage Simplified

In today’s world organizations are constantly looking for data-driven insights. However, it can be difficult to Extract, Store, and Analyze that data as it rapidly grows in scale and scope. BigQuery, Google’s enterprise Data Warehouse, is designed to make large-scale Data Analytics accessible to everyone. When the size of your recorded data increases to Gigabytes, Terabytes, or even Petabytes, your organization needs a more efficient system like a Data Warehouse to manage the massive amount of data.

Google BigQuery offers no shortage of functionality to help you get the most out of Date and Time data, but it can be hard to know what to use and when. This article will introduce you to Google BigQuery and will list the various features that make it unique. Furthermore, this article will also describe the different types of BigQuery Date Functions along with their syntax and examples. Read along to learn more about BigQuery Date Functions!

Table of Contents

• Prerequisites
• Introduction to Google BigQuery
  • Key Features of Google BigQuery
• 8 BigQuery Date Functions: Syntax and Examples
  • Current Date Function
  • Extract Function
  • Date_Add Function
  • Date_Sub Function
  • Date_Diff Function
  • Date_From_Unix_Date Function
  • Last Day Function
  • Parse Date Function
• Conclusion

Prerequisites

• Basic Understanding of SQL.

Introduction to Google BigQuery

Google BigQuery is a fully managed Cloud-based Data Warehouse that is laid upon a Serverless architecture and provides a Big Data Analytics Web Service to process huge datasets over Petabytes of data. Google BigQuery is a Highly Scalable and Serverless Data Warehouse that houses an integrated Query Engine. The Query Engine is capable of executing SQL queries on datasets of mammoth scales in a span of just a few seconds. You can leverage this performance without having to manage any Infrastructure and without creating or rebuilding indexes.

Google leverages its existing Cloud architecture to successfully manage a Serverless design, and it also makes use of various data models that allow users to store dynamic data. It further provides support for Machine Learning (ML) operations by allowing users to take advantage of BigQuery ML functionality. BigQuery ML enables users to develop and train various Machine Learning Models by using built-in SQL capabilities to query data from the desired database. In the later section of this article, you will learn about different types of BigQuery Date Functions along with their syntax and usage.

Key Features of Google BigQuery

Some of the key features of Google BigQuery are as follows:

• Scalability: To provide consumers with true Scalability and consistent Performance, Google BigQuery leverages Massively Parallel Processing and a Highly Scalable Secure Storage Engine. The entire Infrastructure with over a thousand machines is managed by a complex software stack.
• Serverless: The Google BigQuery Serverless model automatically distributes processing across a large number of machines running in parallel, so any company using Google BigQuery can focus on extracting insights from data rather than configuring and maintaining the Infrastructure/Server. 
• Storage: Google BigQuery uses a Columnar architecture to store datasets of mammoth scales. Column-based Storage has several advantages, including better Memory Utilization and the ability to scan data faster than typical Row-based Storage.
• Integrations: Google BigQuery as part of the Google Cloud Platform (GCP) supports seamless integration with all Google products and services. Google also offers a variety of Integrations with numerous third-party services, as well as the functionality to integrate with application APIs that are not directly supported by Google.

8 BigQuery Date Functions: Syntax and Examples

BigQuery Date Functions are used to extract information from Date and Time data in Google BigQuery. Some of the most commonly used BigQuery Date Functions are discussed below:

1) Current Date Function

The Current Date Function is one of the most frequently used BigQuery Date Functions which returns the Current Date with respect to the specified timezone. This function can even be called without an argument. If no timezone is specified, UTC is selected as the default timezone but if the timezone parameter is evaluated to null then NULL is returned as the output.

SYNTAX

CURRENT_DATE([time_zone])

Following is an example of Current Date Function:

SELECT CURRENT_DATE() AS the_date;

+--------------+
| the_date     |
+--------------+
| 2021-09-13   |
+--------------+

2) Extract Function

Extract Function is one of the most important BigQuery Date Functions which is used to extract a specific part from the Date Expression. A complete Date Expression consists of Date, Month, and Year. Suppose you want to extract the month from the Date Expression then the Extract Function can be leveraged. In that case to get the value for the corresponding month. The return type for this Google BigQuery Date Function is INT64.

SYNTAX

EXTRACT(part FROM date_expression)

Following is an example of Extract Function:

SELECT EXTRACT(DAY FROM DATE '2021-09-13') AS the_day;

+---------+
| the_day |
+---------+
| 13      |
+---------+

3) Date_Add Function

The Date_Add Function is one of the key BigQuery Date Functions which is used to add a specific interval to the date passed as an argument to the function. The return type for this function is a Date Expression.

SYNTAX

DATE_ADD(date_expression, INTERVAL int64_expression date_part)

Given below is an example of Date_Add Function:

SELECT DATE_ADD(DATE "2021-09-13", INTERVAL 5 DAY) AS five_days_later;

+--------------------+
| five_days_later    |
+--------------------+
| 2021-09-18         |
+--------------------+

4) Date_Sub Function

Similar to the Date_Add function, Date_Sub Function is used to subtract a specific time interval from the date passed as an argument to the function. The return type for this Google BigQuery Date Function is a Date Expression.

SYNTAX

DATE_SUB(date_expression, INTERVAL int64_expression date_part)

Given below is an example of Date_Sub Function:

SELECT DATE_SUB(DATE "2021-09-13", INTERVAL 5 DAY) AS five_days_ago;

+---------------+
| five_days_ago |
+---------------+
| 2021-09-08    |
+---------------+

5) Date_Diff Function

Date_Diff Function is used to calculate the time interval between the two dates specified. You can select the part of the date that you want to pass as an argument to the function. For example, if you want to calculate the gap between the two dates specified in days, you can pass the date as an argument to the function.

SYNTAX

DATE_DIFF(date_expression_a, date_expression_b, date_part)

Given below is an example of Date_Diff Function:

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

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

6) Date_From_Unix_Date Function

This function is used to interpret a new date since the number of days specified in the argument. The return type of this Google BigQuery Date function is a Date Expression.

SYNTAX

DATE_FROM_UNIX_DATE(int64_expression)

Given below is an example of Date_From_Unix_Date Function:

SELECT DATE_FROM_UNIX_DATE(14238) AS date_from_epoch;

+-----------------+
| date_from_epoch |
+-----------------+
| 2008-12-25      |
+-----------------+

7) Last Day Function

The Last Day Function in Google BigQuery is used to find out the last date of the month. This function receives a date expression as the input and returns the last date of the corresponding month.

SYNTAX

SELECT LAST_DAY(‘DATE’, MONTH)

Given below is an example of Last Day Function:

SELECT LAST_DAY(DATETIME ‘2021–09–13’, MONTH) AS last_day

+-----------------+
| last_day |
+-----------------+
| 2021-09-30      |
+-----------------+

8) Parse Date Function

Parse Date Function is one of the most used BigQuery Date Functions which is used to convert a string representation of a date to a date object.

SYNTAX

PARSE_DATE(format_string, date_string)

Given below is an example of parsing of a string:

SELECT PARSE_DATE("%x", "09/13/21") AS parsed;

+------------+
| parsed     |
+------------+
| 2021-09-13 |
+------------+

If you want to learn more about BigQuery Date Functions, you can click here to check the official documentation.

Conclusion

In this article, you learned about Google BigQuery and the salient features that it offers. You also learned about Google BigQuery Date Functions and how you can use them to extract information associated with Date and Time data in Google BigQuery. With your Data Warehouse, Google BigQuery live and running, you’ll need to extract data from multiple platforms to carry out your analysis. However, integrating and analyzing your data from a diverse set of data sources can be challenging and this is where Hevo Data comes into the picture.

Visit our Website to Explore Hevo

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations such as Google BigQuery, with a few clicks. Hevo Data with its strong integration with 100+ sources (including 40+ free sources) allows you to not only export data from your desired data sources & load it to the destination of your choice, but also transform & enrich your data to make it analysis-ready so that you can focus on your key business needs and perform insightful analysis using BI tools.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!

Share your experience of learning about BigQuery Date Functions. Let us know in the comments section below!