Timezone in BigQuery

Google Cloud Data Analytics
1

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

2

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

3

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

4

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