Converting timestamp to a different timezone in BigQuery

Question

I've combed through Google's documentation multiple times, but I can't seem to find a simple function (within a SELECT query) to convert a UTC timestamp to a different timezone, which in my case is Pacific. For most international timezones, I can simply use TIMESTAMP_SUB or TIMESTAMP_ADD to subtract/add offset hours, but the United States use of daylight savings time complicates things (unnecessarily!).

Did I miss something in the documentation? Or is there some other way to easily convert to another timezone?

Answer 1

The TIMESTAMP type is tied to UTC. When you convert a TIMESTAMP to some other type that isn't tied to a particular timezone, such as STRING, DATE, or DATETIME, you can specify the timezone for the conversion, e.g.:

SELECT EXTRACT(DATE FROM CURRENT_TIMESTAMP()
               AT TIME ZONE 'America/Los_Angeles') AS current_pst_day;

If you want the (current) number of hours between different timezones, you can use CURRENT_DATETIME() with different timezones and take the difference:

SELECT
  time_zone,
  DATETIME_DIFF(CURRENT_DATETIME(time_zone),
                CURRENT_DATETIME(), HOUR) AS hours_from_utc
FROM UNNEST(['America/Los_Angeles', 'America/New_York']) AS time_zone;
+---------------------+----------------+
| time_zone           | hours_from_utc |
+---------------------+----------------+
| America/Los_Angeles | -8             |
| America/New_York    | -5             |
+---------------------+----------------+

To make the offsetting more convenient, you can wrap this into a SQL function, then call it to add the offset to a particular timestamp:

CREATE TEMP FUNCTION OffsetForTimeZone(t TIMESTAMP, time_zone STRING) AS (
  TIMESTAMP_ADD(t, INTERVAL DATETIME_DIFF(CURRENT_DATETIME(time_zone),
                                          CURRENT_DATETIME(), HOUR) HOUR)
);

SELECT OffsetForTimeZone(CURRENT_TIMESTAMP(), 'America/Los_Angeles');

Keep in mind that the result of this is still a TIMESTAMP tied to UTC, albeit offset by the current difference between that and Pacific time.

Answer 2

As an addendum to Elliott's answer, I wanted to test if the timezone math worked around daylight savings changes:

#standardSQL
WITH dates AS (
  SELECT TIMESTAMP('2015-07-01') x, 'summer' season
  UNION ALL SELECT TIMESTAMP('2015-01-01') x, 'winter' season
)

SELECT
  season,
  time_zone,
  DATETIME_DIFF(DATETIME(x, time_zone),
                DATETIME(x), HOUR) AS hours_from_utc
FROM UNNEST(['America/Los_Angeles', 'America/New_York']) AS time_zone
CROSS JOIN dates 
ORDER BY 1,2

It does:

+--------+---------------------+----------------+
| season |      time_zone      | hours_from_utc |
+--------+---------------------+----------------+
| summer | America/Los_Angeles |             -7 |
| summer | America/New_York    |             -4 |
| winter | America/Los_Angeles |             -8 |
| winter | America/New_York    |             -5 |
+--------+---------------------+----------------+

It's even aware that Chile didn't go through DST in 2015:

#standardSQL
WITH dates AS (
  SELECT TIMESTAMP('2014-07-01') x
  UNION ALL SELECT TIMESTAMP('2015-07-01') x
  UNION ALL SELECT TIMESTAMP('2016-07-01') x
)

SELECT
  EXTRACT(YEAR FROM x),
  time_zone,
  DATETIME_DIFF(DATETIME(x, time_zone),
                DATETIME(x), HOUR) AS hours_from_utc
FROM UNNEST(['Chile/Continental', 'America/New_York']) AS time_zone
CROSS JOIN dates 
ORDER BY 2,2


+------+-------------------+----------------+
| f0_  |     time_zone     | hours_from_utc |
+------+-------------------+----------------+
| 2014 | America/New_York  |             -4 |
| 2015 | America/New_York  |             -4 |
| 2016 | America/New_York  |             -4 |
| 2014 | Chile/Continental |             -4 |
| 2015 | Chile/Continental |             -3 |
| 2016 | Chile/Continental |             -4 |
+------+-------------------+----------------+