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?

Solution 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.

Solution 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 |
+------+-------------------+----------------+

Solution 3

Since originally posting my question, I've simply been using the TIMESTAMP_SUB function with an offset to convert the default UTC timestamp to my desired timezone; I didn't use Elliot's elegant solution above because it required me to create a function, which Tableau's custom SQL feature doesn't allow.

For anyone interested, I've now found an alternate solution, albeit ugly as it is. BigQuery does allow built-in timezone offsets (e.g., "America/Los_Angeles") when converting from one format to another. For example, going from timestamp to datetime:

SELECT DATETIME(CURRENT_TIMESTAMP(),"America/Los_Angeles")

The example goes from the current UTC time to Pacific time. However, it's now in a datetime format, which Tableau doesn't recognize as a date/time object. To get it back into timestamp format, I enclosed the function above into a TIMESTAMP function:

SELECT TIMESTAMP(DATETIME(CURRENT_TIMESTAMP(),"America/Los_Angeles"))

Silly, inelegant, and around 20% slower, but it seems to work.

Comments

[dnaeye]

I'm a SQL/BigQuery newb so I didn't know I could even write my own function inside a query! I guess this will have to do unless Google adds this as a built-in function.

[Graham Polley]

Just to add, there is also format_timestamp which returns a String and can handle timezones - cloud.google.com/bigquery/docs/reference/standard-sql/…

[Mikhail Berlyant]

:o) try 'America/Phoenix' and 'America/Los_Angeles' - I was expecting to see one hour difference - but got -7 for both

[Felipe Hoffa]

Google says -7 too :) i.imgur.com/VH8hkyX.png (LA has -8 above.. how did you get -7?)

[Mikhail Berlyant]

but it is 7:04 right now in Phoenix whereas it is just 6:04 in LA. That is what I wanted to point out

[Mikhail Berlyant]

got it. let me check why i suddenly got -7 for both. i just used you code in answer above btw. still thank you for quick check and reply :o)

[Mikhail Berlyant]

oh, I see - i used your second code and it has summer only time, so that is why both returned -7, when i changed it to winter time - i got what I expected

[Graham Polley]

@FelipeHoffa - "It's even aware that Chile didn't go through DST in 2015". That's most impressive!