22 Nov 19 · dan-irving · #Bigquery ·   Bookmark   ×

How to convert a timestamp/date/datetime to a different timezone in Google BigQuery

Google Bigquery runs on UST Clock. For any data insert or update, Bigquery considers the date or timestamp that you send as UST format. 

To convert the UST datetime / timezone to your local timezone, you can use the below functions:

  • DATE(timestamp_expression, timezone)
  • TIME(timestamp, timezone)
  • DATETIME(timestamp_expression, timezone)

The timezone can be provided as UTC-offset (e.g. +02:00) or timezone name (e.g. Europe/Berlin). Refer to the complete list of timezone names.

Example:

To convert to Eastern Time Zone(EST):

SELECT 
current_timestamp() as standard_utc_datetime,
DATE(current_timestamp(), "America/New_York") as date_est,
TIME(current_timestamp(), "America/New_York") as time_est,
DATETIME(current_timestamp(), "America/New_York") as datetime_est

Converting to CST:

  select
DATE(current_timestamp(), "America/Chicago") as date_cst,
TIME(current_timestamp(), "America/Chicago") as time_cst,
DATETIME(current_timestamp(), "America/Chicago") as datetime_cst

Converting to PST:

  select
DATE(current_timestamp(), "America/Tijuana") as date_pst,
TIME(current_timestamp(), "America/Tijuana") as time_pst,
DATETIME(current_timestamp(), "America/Tijuana") as datetime_pst

dan-irving

posted on 22 Nov 19

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds

Copied