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

Posted by Pascal Landau on 2018-04-08 18:00:00

BigQuery provides multiple functions to convert timestamps / dates / datetimes to a different timezone: - DATE(timestamp_expression, timezone) - TIME(timestamp, timezone) - DATETIME(timestamp_expression, timezone)

According to the docu the timezone can be provided as UTC-offset (e.g. +02:00) or timezone name (e.g. Europe/Berlin). See this list of IANA timezone offsets and names.

The converted dates/times can than be formatted with via - FORMAT_DATE(format_string, date_expression) - FORMAT_TIME(format_string, time_expression) - FORMAT_DATETIME(format_string, datetime_expression)


  DATETIME(timestamp, "Europe/Berlin") as datetime_berlin,
  DATE(timestamp, "Europe/Berlin") as date_berlin,
  TIME(timestamp, "Europe/Berlin") as time_berlin,
  FORMAT_DATETIME("%F %X", DATETIME(timestamp, "Europe/Berlin")) as formatted_date_time_berlin

Working Example

Run on BigQuery

Open in BigQuery Console

BigQuery Console: Convert timestamp to different timezone example


Use cases

BigQuery displays data usually in UTC. That leads to problems when using date formatting functions because dates and times can be off. Converting the datetimes prior formatting into the correct timezone solves those issues.

Common formats: ```` FORMAT_TIMESTAMP("%F %X", timestamp) # %Y-%m-%d %H:%M:%S => 2018-04-08 18:28:01 FORMAT_DATETIME("%F %X", DATETIME(timestamp, "Europe/Berlin")) # %Y-%m-%d %H:%M:%S => 2018-04-08 18:28:01 FORMAT_DATE("%F", DATE(timestamp, "Europe/Berlin")) # %Y-%m-%d => 2018-04-08 FORMAT_DATE("%V", DATE(timestamp, "Europe/Berlin")) # calendar week => 14 FORMAT_TIME("%T", DATETIME(timestamp, "Europe/Berlin")) # %H:%M:%S => 18:28:01 ```

Wanna stay in touch?

Since you ended up on this blog, chances are pretty high that you're into Software Development (probably PHP, Laravel, Docker or Google Big Query) and I'm a big fan of feedback and networking.

So - if you'd like to stay in touch, feel free to shoot me an email with a couple of words about yourself and/or connect with me on LinkedIn or Twitter or simply subscribe to my RSS feed or go the crazy route and subscribe via mail and don't forget to leave a comment :)

Subscribe to posts via mail

We use Mailchimp as our newsletter provider. By clicking subscribe, you acknowledge that your information will be transferred to Mailchimp for processing. Learn more about Mailchimp's privacy practices here.
Waving bear