When dealing with databases, handling dates and times becomes an essential task. In Google BigQuery, there are built-in functions that make working with dates and times straightforward. This guide outlines key features and functionalities in BigQuery to assist you in managing dates and times effectively.

Understanding Date and Time Data Types #

Google BigQuery has distinct data types to handle dates and times:

  • DATE: Represents a logical calendar date. Format: YYYY-[M]M-[D]D.
  • TIME: Represents a time of day. Format: [H]H:[M]M:[S]S[.DDDDDD].
  • DATETIME: Represents a date and time without a timezone. Format: YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.DDDDDD]].
  • TIMESTAMP: Represents an absolute point in time with microsecond precision.

Using Date and Time Functions #

BigQuery provides a variety of functions to manipulate and convert these data types:

Extraction Functions #

  • EXTRACT(): Retrieves subfields such as year, month, day, hour, minute, and second from a DATE, DATETIME, or TIMESTAMP value.

Conversion Functions #

  • CAST(): Converts one type to another, including date and time types.
  • FORMAT_TIMESTAMP(), FORMAT_DATE(), and FORMAT_DATETIME(): Formats a timestamp, date, or datetime.

Interval Calculation Functions #

  • DATE_ADD(), DATE_SUB(): Adds or subtracts a specified interval to or from a date.
  • TIMESTAMP_DIFF(): Returns the difference between two TIMESTAMP values.

Managing Time Zones #

By default, TIMESTAMP data type in BigQuery is in UTC. However, you can use the FORMAT_TIMESTAMP() function to display timestamps in any time zone.

To convert a timestamp to a different time zone, you can use the TIMESTAMP function in combination with a string that specifies both the original timestamp and the desired time zone.

Dealing with Current Date and Time #

BigQuery provides CURRENT_DATE(), CURRENT_DATETIME(), and CURRENT_TIMESTAMP() functions to get the current date, datetime, and timestamp, respectively.

In conclusion, BigQuery provides a rich set of functions and features that make working with dates and times quite straightforward. By understanding and effectively using these features, you can ensure your queries are accurate and efficient.