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.


To convert to Eastern Time Zone(EST):

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:

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:

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


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