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. 

Table of contents

• Introduction to BigQuery
• Date functions of BigQuery SQL
• DATE_ADD BigQuery
• Other Date functions in BigQuery SQL
  • DATE_SUB
  • CURRENT_DATE
  • EXTRACT
• Conclusion

Introduction to BigQuery

Google’s BigQuery is a 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). Other Data Warehouse solutions from major public Cloud providers, such as Amazon Web Services’ Redshift or Microsoft’s Azure SQL Data Warehouse, are in contention with Google’s BigQuery.

BigQuery is an enterprise data warehouse, provided as a SAAS offering by Google, that allows storing and querying huge volumes of data. Its primarily used to analyze petabytes of data using ANSI SQL at blazing-fast speeds.  

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.

An interesting case happens in a leap year. 

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 the above illustration, if we subtract 1 YEAR from Feb 29, 2024, the resulting date is Feb 28, 2023. 

In other simple cases, the behavior is as expected.

As the above example illustrated, adding 25 days to 7th September 2021, will result in 2nd October 2021. 

2. CURRENT_DATE

Syntax: CURRENT_DATE([time_zone])

This function returns the current date as of the specified or default timezone. Parentheses are optional when called with no arguments.

3. EXTRACT

Syntax: EXTRACT(part FROM date_expression) – 

This function returns the value corresponding to the specified date part. The part must be one of one f the following:

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

CONCLUSION

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. You have also learned about other Date Queries available in BigQuery. 

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.

Visit our Website to Explore Hevo

Hevo Data with its strong integration with 150+ Data sources (including 30+ 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.

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing Hevo Price, which will assist you in selecting the best plan for your requirements.