Timezone in BigQuery

[LucasYang]

There are lots of IDs with different timestamp and time zone information like America/New_York or Asia/Shanghai, and now I want to convert them all to UTC+0. I know function date_time(timestamp, "America/New_York")  , but I cant convert them based on the filed information because it is typed-in information with quotations

[mars124 · Accepted solution]

Please show at least 3 example rows of your data with the relevant columns.

[LucasYang]

Hi mars, thank you for your reply. These are some data I get:

id       timestamp     country        timezone

49173450     2022-06-11 02:33:58.092218   United States   America/Chicago
39385765     2022-06-11 02:34:19.365086    United States   America/New_York
10485757     2022-06-11 02:43:26.638105     Japan                 Asia/Tokyo

I want to analyze users' behavior in a whole day across the world, like their actions in daytime or nighttime. But I only get UTC+0 timestamp and their timezone.  I know the function date_time(timestamp, "America/New_York")  but it doesn't fit because I have to type in the timezone instead of as a parameter.   
 

Thank you 

[mars124]

Try this:

with test as (
  select
    '2022-06-11 02:33:58.092218' as timestamp_column, 'America/Chicago' as timezone_column
  union all
  select
    '2022-06-11 02:34:19.365086', 'America/New_York'
  union all
  select
    '2022-06-11 02:43:26.638105', 'Asia/Tokyo'
)

select *,

  -- when timestamp_column is the local datetime
  timestamp(timestamp_column,timezone_column) as utc_time,

  -- when timestamp_column is the UTC datetime
  datetime(timestamp(timestamp_column),timezone_column) AS local_datetime
  
from test