CODEWITHSUNDEEP
google-bigquery
Kurt Maile
Kurt Maile

Reputation: 1267

BigQuery String to Timestamp retaining Timezone in Source

I've looked at other similar posts and not sure they susinctly answer this - maybe they do, sorry if I have missed any points that could answer this.

I have a string

"2017-06-16T10:34:57.705+01:00"

How do I convert this to a 'Timestamp' Type from the String, but retaining precision (the milliseconds) and the timezone offset? 

TIMESTAMP("2017-06-16T10:34:57.705+01:00")

converts to a Timestamp in UTC and looses the milliseconds precision, I want a timestamp type but retaining all the precison of the local date with the timezone?

FORMAT_TIMESTAMP("%Y-%m-%dT%H:%M:%E*S%Ez",PARSE_TIMESTAMP("%Y-%m-%dT%H:%M:%E*S%Ez", "2017-06-16T10:34:57.705+01:00"),"Europe/London")

This retains the precison and has the offset but is a String - then I lose this trying to convert to a timestamp again TIMESTAMP(x)!

I feel Im going around in circles any help appreciated thanks!

Cheers

Upvotes: 2

Views: 1744

Answers (2)

Willian Fuks
Willian Fuks

Reputation: 11787

Just as a complement, you are not actually losing precision when you apply the PARSE_TIMESTAMP as you can check by using the UNIX_MILLIS function:

WITH data AS(
  SELECT "2017-06-16T10:34:57.705+01:00" as date union all
  SELECT "2017-06-16T10:34:57.999+01:00" as date
)

SELECT
  date,
  UNIX_MILLIS(PARSE_TIMESTAMP("%Y-%m-%dT%H:%M:%E*S%Ez", date, "Europe/London")) millis_date
FROM data

Results in:

Row date                            millis_date  
1   2017-06-16T10:34:57.999+01:00   1497605697999    
2   2017-06-16T10:34:57.705+01:00   1497605697705

Upvotes: 1

Elliott Brossard
Elliott Brossard

Reputation: 33705

The TIMESTAMP type is some point in time relative to the UNIX epoch. It has microsecond precision and does not encode a timezone, though the TIMESTAMP-related functions use UTC by default, e.g. for extracting a DATE.

It's not clear why you want to retain the original timezone of the timestamps, but one thing you could do would be to keep an integer column with the offset from UTC, which you can compute using TIMESTAMP_DIFF between the input timestamp ignoring Its timezone in the string and the input timestamp using its timezone in the string.

For the precision, you can check for equality between the provided timestamp and the result of TIMESTAMP_TRUNC with e.g. MILLISECOND to see what the precision is. I don't know what you want to do with the information, but you could use a string or integer to represent the precision as well.

Upvotes: 1

Related Questions