Great Google BigQuery Date Functions to Know
Below the video are great date functions on Google BiqQuery. However, you can first witness a two-minute video that joins a BigQuery table with tables from Databricks, Snowflake, Redshift, Azure Synapse, Vertica, Teradata, Oracle, SQL Server, DB2, Yellowbrick, MySQL, Postgres, Microsoft Access, and Excel in a single federated query.
You can now download our books on Synapse, Redshift, Google BigQuery, DB2, Oracle, Yellowbrick, Vertica, MySQL, Postgres, and more for FREE.
You can download the PDFs from our CoffingDW.com website immediately.
Here is how you do it. Go to CoffingDW.com and click on Books from the top right menu. You will then be in the CoffingDW bookstore, where you will see the Snowflake book. Add the Snowflake book to your cart.
Add any book you want to your cart.
When you check out, you use the coupon FRIEND2023.
You won’t have to enter any credit card information. Just put in your name and email, and the price will be discounted to zero. You can then download the book immediately at no cost to you.
Below are the keywords to get the date, time, and timestamp. These are reserved words that the system will deliver to you when requested.
Add or subtract a number from a date, and you are adding or subtracting days.
The query example below shows how the date is constructed in the year, month, and day. It also shows how the DATE command extracts the date portion from a timestamp. Finally, notice the third date is December 31st, but because of the use of the timezone, the extract date is December 30th.
The examples below show you how to format dates.
The Extract command extracts portions of a date, time, or timestamp.
The below examples show how robust the EXTRACT command is at extracting portions of a date, time, or timestamp. We also use the CASE logic to determine the day of the week.
DATE_DIFF returns the number of the whole specified date_part intervals between two dates.
DATE_TRUNC truncates the date to the specified granularity, such as Day, Week, Week, Month, Quarter, Year, ISOWEEK, and ISOYEAR. Below, we have the date of ‘2023-05-05’ and return the dates at the beginning of the month, year, quarter, and the previous Monday.
The DATE_ADD will take an interval and add it to the date. Above, our example is adding different criteria for five days, one month, and two years to the current date. Special handling is done automatically for MONTH, QUARTER, and YEAR parts when the date is at (or near) the last day of the month. If the resulting month has fewer days than the original date’s day, the result day is the last day of the new month.
The example below displays the current_date and the first or last day of other months. We have the month’s first and final day, the previous month’s last day, and the next month’s first day.
Tom Coffing, better known as Tera-Tom, founded Coffing Data Warehousing, where he has been CEO for the past 30 years. Tom has written over 90 books on Architecture, performance tuning, and SQL on all relevant systems. Tom has taught over 1,000 classes worldwide, and he is the designer of the Nexus, which celebrates 20 years of brilliantly integrating all databases.
