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:

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

Code

#standardSQL
SELECT
  DATETIME(timestamp, "Europe/Berlin") as datetime_berlin,
  DATE(timestamp, "Europe/Berlin") as date_berlin,
  TIME(timestamp, "Europe/Berlin") as time_berlin,
  FORMAT_DATETIME("%c", DATETIME(timestamp, "Europe/Berlin")) as formatted_date_time_berlin
FROM
  table

Working Example

Run on BigQuery

Open in BigQuery Console

BigQuery Console: Convert timestamp to different timezone example

Links

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_DATETIME("%c", 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, Twitter or Facebook - or simply subscribe to my RSS feed and leave a comment ;)

Waving bear

Comments