DATE_ADD function in BigQuery – Syntax and Queries

BigQuery offers a wide range of functionality to help you get the most out of date and time data. But sometimes, it can be tiring to understand some of its date functions and execution.

In this blog, you will understand two important functions of BigQuery’s SQL, namely Date_Add BigQuery and Date_Sub BigQuery functions, including their peculiarities along.

You will also get to know other Date Queries available in BigQuery. 

Google BigQuery Performance Optimization: Best Practices Guide

Download for Free

Introduction to BigQuery

• Powerful Business Intelligence (BI) platform that works as a “Big Data as a Service” solution.
• Google BigQuery is a tool that creates real-time analytic reports of Big Data to help you generate useful insights to make effective business decisions.
• BigQuery is a fully managed, serverless SQL Data Warehouse that facilitates speedy SQL queries and interactive analysis of large datasets (in the order of Terabytes or Petabytes).

Date functions of BigQuery SQL

The standard SQL in BigQuery supports an object named DATE, which represents a logical calendar date, without any time. Its canonical(simplest) form is YYYY-[M]M-[D]D, here YYYY represents a Four-digit year, [M]M represents a One or two-digit month, and [D]D represents a One or two-digit day. The values of these variables can range from 0001-01-01 to 9999-12-31. 

The data in BigQuery can be queried with various parameters, including date/time ranges. To manipulate the DATE datatype and to provide time ranges, some Date functions are provided in Standard SQL. 

DATE_ADD BigQuery

Syntax: DATE_ADD(date_expression, INTERVAL int64_expression date_part)

The DATE_ADD BigQuery is a function that adds the specified time interval to a DATE. The parameters to this function, also called date_part values, can be the date, week( 7 days), month, quarter(3 months), and Year. 

SELECT DATE_ADD(DATE "2021-01-01", INTERVAL 2 DAY) AS two_days_later; 

The DATE_ADD BigQuery gives the following result.

+——————–+
| two_days_later    |
+——————–+
| 2021-01-03         |
+——————–+, in the YYYY-MM-DD format. 

In the same way,  if we use DATE_ADD BigQuery to add 25 days to 7th September 2021, we get 2nd October 2021.

 If the DAY part of the DATE used as input in the DATE_ADD BigQuery function, is close to the end of the month ( say 30 ) and the number of days added is close to a month ( say we add 29 days or 1 MONTH), then IF the resulting month has fewer days than the original date’s day, the resulting date will be the last day of that month. 

This is illustrated in the following examples.

In the above example, 1 MONTH is added to 2021-01-31. Now, in many computations a 30 day month is assumed, but not here. Here,  the resulting date is the last day of February 2021, as the input date is close to the end of January 2021. 

To clarify this further, let’s add 1 Month to Feb 28, 2021.

Since March has more days than February, now the answer is March 28, 2021. 

The Date_ADD BigQuery function simply adds a month to the input date, as the following month has more days than the input month. But, February has 28 days on, it just adds 28 days to the input date and returns 28 March ( not 31st March). 

Whereas if you Date_Add 1 month to March 31, you get the last day of the next ( April ) month.

As a corollary, if you subtract a month from March 31, using Date_Sub, you will get the last day of the previous month, i.e. Feb 28.

As seen above, if you add Date_Add 1 month to January 30, 2024, the resulting date is February 29, 2024.

Please note that the input day is NOT 31st. Also, note that the resulting day is  NOT 28. It just gives you the last day of February of 2024, when the input day is close to end of the month ( near to the end of January here), and you add a month to it.

Now, you know that 2024 is a leap year, and the last day in February of 2024 will be 29. But, if you use Date_ADD BigQuery for adding 1 YEAR to February 28, 2023, you will get February 28, 2024, NOT Feb 29. 

On the other hand, if you Subtract 1 MONTH from March 31st, 2024, using Date_Sub, you get the last day of the February of 2024.

The above examples illustrate the peculiarities of the Date_ADD BigQuery and the Date_Sub BigQuery functions in BigQuery SQL.

Other Date functions in BigQuery SQL

1. DATE_SUB

Syntax: DATE_SUB(date_expression, INTERVAL int64_expression date_part)

This function, Subtracts a specified time interval from a DATE. Again, Special handling is required when the input date’s DAY is at (or near) the last day of the month. If the resulting month has fewer days than the original date’s day, then the resulting date is the last date of that month. 

For example, If INPUT DAY is 31st and the previous month just had 30 days, subtracting a MONTH will give you the last day of the previous month. i.e. It will not just subtract the average 30 days to give you a result. 

You can also subtract year(s) from a date using Date_Sub.

In other simple cases, the behavior is as expected.

2. CURRENT_DATE

Syntax: CURRENT_DATE([time_zone])

3. EXTRACT

Syntax: EXTRACT(part FROM date_expression) – 

• DAYOFWEEK
• DAY
• DAYOFYEAR
• WEEK
• WEEK(<WEEKDAY>)
• MONTH
• QUARTER
• YEAR 

CONCLUSION

1. In this blog, you would have understood the two most important functions of BigQuery’s SQL, Date_Add BigQuery and Date_Sub BigQuery functions, including their peculiarities along.
2. You have also learned about other Date Queries available in BigQuery.