Convert timestamp/date/datetime to different timezone in BigQuery

convert-timestamp-to-different-timezone.sql

#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