BigQuery: String to Timestamp

2
votes

I have a timestamp from the source that has been loaded to BQ as a string. I'd like to write a query in BigQuery that will return timestamp in the following format 2020-01-06 11:09:14.000-0600. Here is the current format of the string field: 2020-01-06T11:09:14.000-0600, 2018-10-01T15:45:59.000-0500, etc.

I have tried the following: SELECT parse_timestamp ("%Y-%m-%dT%H:%M:%S.%E3S", start_timestamp, "America/Chicago"), FROM bqtable

The goal is to perform arithmetic on the timestamp fields.

Any feedback is appreciated. Thank you.

1
google-bigquery

1 Answers

2
votes

I think the %S and %E3S% are conflicting, as they both are parsing the seconds part of the string.

Try this:

with data as (
   select '2020-01-06T11:09:14.000-0600' as ts_string union all select '2018-10-01T15:45:59.000-0500'
)
select ts_string, parse_timestamp ("%Y-%m-%dT%H:%M:%E3S%z", ts_string, "America/Chicago") as ts
from data