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
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.
Download the Cheatsheet on How to Set Up High-performance ETL to BigQuery
Learn the best practices and considerations for setting up high-performance ETL to BigQuery
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.
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.
A fully managed No-code Data Pipeline platform like Hevo Data helps you integrate and load data from 150+ different sources (including 30+ free sources) to a Data Warehouse such as Google BigQuery or Destination of your choice in real-time in an effortless manner. Hevo with its minimal learning curve can be set up in just a few minutes allowing the users to load data without having to compromise performance. Its strong integration with umpteenth sources allows users to bring in data of different kinds in a smooth fashion without having to code a single line.
Get Started with Hevo for Free
Check out some of the cool features of Hevo:
Sign up here for a 14-Day Free Trial!
- Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
- Transformations: Hevo provides preload transformations through Python code. It also allows you to run transformation code for each event in the Data Pipelines you set up. You need to edit the event object’s properties received in the transform method as a parameter to carry out the transformation. Hevo also offers drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few. These can be configured and tested before putting them to use.
- Connectors: Hevo supports 100+ integrations to SaaS platforms, files, Databases, analytics, and BI tools. It supports various destinations including Google BigQuery, Amazon Redshift, Snowflake Data Warehouses; Amazon S3 Data Lakes; and MySQL, SQL Server, TokuDB, DynamoDB, PostgreSQL Databases to name a few.
- Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
- 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
- Scalable Infrastructure: Hevo has in-built integrations for 100+ sources (including 30+ free sources) that can help you scale your data infrastructure as required.
- 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
Other Date functions in BigQuery SQL
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.
This function returns the current date as of the specified or default timezone. Parentheses are optional when called with no arguments.
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:
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.