Instantly share code, notes, and snippets.

Embed
What would you like to do?
Convert timestamp/date/datetime to different timezone in BigQuery
#standardSQL
# Convert date / time to a different timezone in BigQuery; standard-sql; 2018-04-08
# @see http://www.pascallandau.com/bigquery-snippets/convert-timestamp-date-datetime-to-different-timezone/
WITH examples AS (
SELECT TIMESTAMP("2018-04-08T15:50:10+00:00") AS timestamp # Daylight saving time
UNION ALL SELECT TIMESTAMP("2018-03-08T15:50:10+00:00") # Standard time
)
SELECT
timestamp,
DATETIME(timestamp) as datetime,
DATE(timestamp) as date,
TIME(timestamp) as time,
DATETIME(timestamp, "Europe/Berlin") as datetime_berlin,
DATE(timestamp, "Europe/Berlin") as date_berlin,
TIME(timestamp, "Europe/Berlin") as time_berlin
FROM examples
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment