我看过其他类似的帖子,不确定他们是否会回答这个问题 - 也许他们会这样做,如果我错过了可以回答这个问题的任何问题,抱歉。
我有一个字符串
2017-06-16T10:34:57.705 + 01:00如何将此转换为String中的'Timestamp'类型,但保留精度(毫秒)和时区偏移量?
TIMESTAMP(2017-06-16T10:34:57.705 + 01:00)转换为UTC的时间戳,并且失去毫秒精度,我想要一个时间戳类型,但保留本地日期与时区的所有精度?
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)这保留了精度并且有偏移量,但是是一个字符串 - 然后我失去了尝试转换为时间戳再次TIMESTAMP(x)!
我觉得我在圈子里任何帮助赞赏谢谢!
干杯
解决方案在应用 PARSE_TIMESTAMP 时会失去精度,您可以使用 UNIX_MILLIS 函数进行检查:
WITH数据AS( SELECT2017-06-16T10:34:57.705 + 01:00as date union all SELECT 2017-06-16T10:34:57.999 + 01:00as date b $ b) SELECT date, UNIX_MILLIS(PARSE_TIMESTAMP(%Y-% m-%dT%H:%M:%E * S%Ez,date,Europe / London))millis_date FROM data $ p $ $ p $ b < 06-16T10:34:57.999 + 01:00 1497605697999 2 2017-06-16T10:34:57.705 + 01:00 1497605697705
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
解决方案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 dataResults 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
更多推荐
BigQuery字符串,用于在源中保留时区的时间戳
发布评论