CODEWITHSUNDEEP
dateselectgoogle-cloud-platformgoogle-bigquerytimestamp
santhosh
santhosh

Reputation: 509

How to convert string to timestamp into bigquery

I have a BQ stage table with a string column that holds timestamp format like below:

2000-01-01 00:00:00.0000000 -08:00
2010-10-01 00:00:00.0000000 -07:00

How to load this strings into another BQ table which has timestamp as type.

Upvotes: 0

Views: 6632

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Canonical form of timestamp is YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.DDDDDD]][time zone] and can consist only up to six fractional digits (microsecond precision)

So, in case you data actually has 6 (not seven) digits - you can use either of below 

PARSE_TIMESTAMP('%F %H:%M:%E6S %Ez', ts)

OR 

PARSE_TIMESTAMP('%F %H:%M:%E*S %Ez', ts)

Upvotes: 1

GMB
GMB

Reputation: 222402

Your string looks like a legitimate literal timestamp string, so doesn't cast() just work?

cast(my_string_col as timestamp) my_timestamp_col

Otherwise, you need parse_timestamp():

parse_timestamp(''%Y-%m-%d %H:%M:%E#7 %Ez', my_string_col) my_timestamp_col

Upvotes: 0

Related Questions