CODEWITHSUNDEEP
sqlgoogle-bigquery
dnaeye
dnaeye

Reputation: 327

Converting timestamp to a different timezone in BigQuery

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?

Upvotes: 3

Views: 26792

Answers (4)

monty jango
monty jango

Reputation: 1

This should work:

SELECT CURRENT_DATE("America/Los_Angeles")

Upvotes: 0

dnaeye
dnaeye

Reputation: 327

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.

Upvotes: 1

Felipe Hoffa
Felipe Hoffa

Reputation: 59325

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

Upvotes: 2

Elliott Brossard
Elliott Brossard

Reputation: 33765

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.

Upvotes: 5

Related Questions