how to parse timestamp with nanoseconds

user_3236835 0
Viewed 213

We have a timestamp like 2021-05-03T14:25:30.195737792Z in our data and the Big Query (```CAST('2021-54-03T14:25:30.195737792Z' AS timestamp)```) fails with the following error:

Invalid timestamp: '2021-05-03T14:25:30.195737792Z'

How can I convert the timestamp with nanoseconds to e.g. microseconds in UTC format?

google-bigquery timestamp
1 Answers

Try below

select ts_as_string, 
  timestamp(regexp_replace(ts_as_string, r'([^.]+)(.)?(\d)?(\d)?(\d)?(\d)?(\d)?(\d)?(\d)?(\d)?(\d)?', r'\1\2\3\4\5\6\7\8')) ts_as_timestamp
from your_table

if applied to dummy data as in your example - output is

enter image description here

The shorter version is

timestamp(regexp_replace(ts_as_string, r'([^.]+)(.)?(\d{0,6})(\d)*', r'\1\2\3'))
user_52219440
Related