DATE_ADD function in BigQuery – Syntax and Queries Explained

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. 

Why Date Manipulation Matters in BigQuery

Handling date-based data efficiently is essential for tasks like calculating durations, grouping data by time intervals, and more. BigQuery simplifies these operations with powerful date functions.

For instance, the DATE_ADD function enables you to add or subtract days, months, or years to a date, allowing for seamless date transformations. For example, if you need to analyze sales from the past 30 days, you can easily filter your dataset using this function.

Beyond DATE_ADD, BigQuery offers other versatile date functions like DATE_DIFF for calculating time differences, DATE_TRUNC for rounding dates to specific intervals, and DATE_PART for extracting date components. These tools streamline complex date calculations without the need for multiple queries or manual effort.

Additionally, BigQuery’s integration with Google Cloud services such as Dataflow ensures that your data is prepped, transformed, and ready for analysis with accurate and clean timestamps.

By leveraging BigQuery’s robust date manipulation functions, you can unlock valuable insights, optimize your data workflows, and drive data-informed decisions effortlessly.

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. 

Date Add BigQuery Add Days to Date

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

Date Add BigQuery Adding Months to Date

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 

Best Practices and Use Cases of the DATE_ADD() Function

The BigQuery DATE_ADD() function is a versatile tool for working with date-based data. Here are some best practices and practical use cases to maximize its effectiveness:

Best Practices

1. Understand Your Time Intervals: Ensure you know whether to add days, months, or years for accurate calculations.
2. Combine with Filters: Use DATE_ADD() in WHERE clauses to filter datasets by specific time ranges.
3. Optimize Queries: Use the function within calculated fields to reduce the need for multiple query runs.
4. Format Results: Pair DATE_ADD() with formatting functions to display results in user-friendly formats.
5. Test Before Production: Run small-scale queries first to ensure the function behaves as expected with your data.

Use Cases

1. Sales Analysis: Filter sales data for the last 30 days by adding or subtracting days from the current date.
2. Subscription Tracking: Calculate renewal dates by adding subscription durations (e.g., months) to start dates.
3. Employee Metrics: Analyze employee tenure by adding joining dates to track milestones like probation completion.
4. Seasonal Trends: Add or subtract months to analyze data for specific seasons or quarters.
5. Event Planning: Determine future deadlines or past anniversaries by manipulating event dates.

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. 

BigQuery makes Business Analysis more efficient through intuitive and easy-to-use services. Moreover, analyzing and visualizing data from multiple sources in BigQuery can be cumbersome. This is where Hevo comes in. Hevo Data, a No-Code Data Pipeline Platform, empowers you to ETL your data from a multitude of sources to Databases, Data Warehouses, or any other destination of your choice in a completely hassle-free & automated manner. 

Try a 14-day free trial and experience the feature-rich Hevo suite firsthand. Also, check out our unbeatable pricing to choose the best plan for your organization.

Frequently Asked Questions