Parsing timestamp in BigQuery with two different timezones

user_17677629 0
Viewed 61

I'm having trouble parsing this datetime field in BigQuery that takes in two different time zone formats.

It is stored as a string. The timestamps look like either of these.

DateTime:
Thu Mar 03 2022 18:18:38 GMT+0000 (GMT)
Thu Mar 03 2022 00:04:32 GMT-0800 (Pacific Standard Time)

What I want:

DateTime:
Thu Mar 03 2022 10:18:38 GMT-0800 (Pacific Standard Time)
Thu Mar 03 2022 00:04:32 GMT-0800 (Pacific Standard Time)

I get errors with datetime and parse_timestamp trying to do something like this:

parse_timestamp("%a %b %d %E4Y %T", DateTime , 'US/Pacific')

How can I get this to work?

google-bigquery datetime
1 Answers

Consider below options

select *, 
  datetime(parse_timestamp("%a %b %d %Y %T GMT%Z", regexp_replace(DateTime, r' \([ \w]+\)', '')), 'US/Pacific'),
  format_datetime("%a %b %d %Y %T", datetime(parse_timestamp("%a %b %d %Y %T GMT%Z", regexp_replace(DateTime, r' \([ \w]+\)', '')), 'US/Pacific')), 
from your_table

if applied to sample data in your question - output is

enter image description here

user_52219440
Related