CODEWITHSUNDEEP
google-bigquerytimezone
KayEss
KayEss

Reputation: 419

Convert local datetime to UTC BigQuery

My datetime data is stored as a local datetime and local timezone, given two columns. First column contains local datetime and a second one local timezone (see the picture below):

enter image description here

How can i create a single column with datetime in UTC?

So far i've tried:

SELECT
        localdatetime, localtimezone,
        PARSE_TIMESTAMP('%b %d, %Y, %r', LocalDateTime, "UTC") AS parsed_datetime
    FROM `xx`.yy.zz
    WHERE Localtimezone = "Europe/Ljubljana"

But the result i get is obviously wrong, since BQ doesn't know from which timezone it is converting.

enter image description here

Upvotes: 0

Views: 2011

Answers (1)

Daniel Zagales
Daniel Zagales

Reputation: 3034

BigQuery will automatically convert to UTC, so what you need to specify is really the timezone you are converting from.

Try the following instead:

select 
    localdatetime 
    , localtimezone 
    , PARSE_TIMESTAMP('%b %d, %Y, %r', LocalDateTime, localtimezone ) AS parsed_datetime
from sample_data

For more information refer to the documentation here https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#parse_timestamp

Upvotes: 2

Related Questions