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

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

To convert the UTC 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