Convert list of times from different timezones into UTC with BigQuery

Question

I have a list of stores that are all in different timezones. I want to take these stores and convert them all to be using UTC instead of their local timezone. I can see how I can do the opposite but how would I be able to do this via bigQuery?

My thought was to use something like this and timestamp_sub to do this manually.

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;

Opposite example

example data:

Location	time_zone	datetime
1	US/Eastern	2016-02-12T23:04:00Z
2	US/Alaska	2016-02-12T23:04:00Z
3	America/Puerto_Rico	2016-02-12T16:47:00Z
4	US/Pacific	2016-02-12T20:11:00Z
5	US/Central	2016-02-12T20:14:00Z
6	US/Arizona	2016-02-12T12:22:00Z
7	US/Mountain	2016-02-12T21:06:00Z
8	US/Hawaii	2016-02-12T19:25:00Z

Answer 1

With the function timestamp, you can convert the date to UTC.

This function supports an optional parameter to specify a time zone. If no time zone is specified, the default time zone, UTC, is used.

You can see this example with some example data from different time zones.

with Dates as (
 
   SELECT "Store 1" as store,"2022-01-02 00:00:00" as time, "America/Los_Angeles" as time_zone
   UNION ALL
   SELECT "Store 2" as store,"2022-01-03 00:00:00" as time, "Europe/Berlin" as time_zone
   UNION ALL
   SELECT "Store 3" as store,"2022-01-03 00:00:00"as time, "America/New_York" as time_zone
)
 
select
    store
   ,time_zone
   ,time
   , TIMESTAMP(time, time_zone  ) AS UTC
   , DATETIME_DIFF(CURRENT_DATETIME(time_zone),CURRENT_DATETIME(), HOUR) AS hours_from_utc
   from Dates

You can see the output data.