BigQuery converting to a different timezone
Standard SQL in BigQuery has built-in functions:
DATE(timestamp_expression, timezone)
TIME(timestamp, timezone)
DATETIME(timestamp_expression, timezone)
Example:
SELECT
original,
DATETIME(original, "America/Los_Angeles") as adjusted
FROM sometable;
+---------------------+---------------------+
| original | adjusted |
+---------------------+---------------------+
| 2008-12-25 05:30:00 | 2008-12-24 21:30:00 |
+---------------------+---------------------+
You can use standard IANA timezone names or offsets.
To those that stumble here:
How to convert a timestamp to another timezone?
Given that TIMESTAMP values, once constructed, are stored as UTC, and that TIMESTAMP does not have a constructor (TIMESTAMP, STRING), you can convert a timestamp to another time zone by transforming it first to a DATETIME and then constructing the new TIMESTAMP from the DATETIME in the new timezone:
SELECT TIMESTAMP(DATETIME(timestamp_field, '{timezone}'))
Example:
SELECT
input_tz,
input,
'America/Montreal' AS output_tz,
TIMESTAMP(DATETIME(input,'America/Montreal')) AS output
FROM (
SELECT 'US/Pacific' AS input_tz, TIMESTAMP(DATETIME(DATE(2021, 1, 1), TIME(16, 0, 0)), 'US/Pacific') AS input
UNION ALL
SELECT 'UTC' AS input_tz, TIMESTAMP(DATETIME(DATE(2021, 1, 1), TIME(16, 0, 0)), 'UTC') AS input
UNION ALL
SELECT 'Europe/Berlin' AS input_tz, TIMESTAMP(DATETIME(DATE(2021, 1, 1), TIME(16, 0, 0)), 'Europe/Berlin') AS input
) t
results in:
Row | input_tz | input | output_tz | output |
---|---|---|---|---|
1 | US/Pacific | 2021-01-02 00:00:00 UTC | America/Montreal | 2021-01-01 19:00:00 UTC |
2 | UTC | 2021-01-01 16:00:00 UTC | America/Montreal | 2021-01-01 11:00:00 UTC |
3 | Europe/Berlin | 2021-01-01 15:00:00 UTC | America/Montreal | 2021-01-0110:00:00 UTC |
How to strip time zone info from a DATETIME value?
DATETIME in BigQuery are time zone naive, such that they do not contain timezone info. This being said, if you have business knowledge that allows you to know the timezone of a DATETIME, you can strip that timezone offset by converting it to a TIMESTAMP with the known timezone:
SELECT TIMESTAMP(datetime_value, '{timezone}')
Given that the TIMESTAMP stores the value in UTC, you can then re-convert to DATETIME if that's your preferred method of storage, but now you'll know that your DATETIME is in UTC :)
Hopefully this can be helpful! :)
As of September 2016 BigQuery has adopted standard SQL and you can now just use the "DATE(timestamp, timezone)" function to offset for a timezone. You can reference their docs here:
BigQuery DATE docs