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

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)

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("%F %X", DATETIME(timestamp, "Europe/Berlin")) as formatted_date_time_berlin
FROM
  table

Working Example

Run on BigQuery

Open in BigQuery Console

Links

• Gist on Github
• Example on BigQuery
• Answer to "BigQuery converting to a different timezone" on Stackoverflow

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 :)

Comments