BigQuery字符串,用于在源中保留时区的时间戳 [英] BigQuery String to Timestamp retaining Timezone in Source
问题描述
我看过其他类似的帖子,不确定他们是否会回答这个问题 - 也许他们会这样做,如果我错过了可以回答这个问题的任何问题,抱歉。
我有一个字符串
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(
$ p $
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
$ 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 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
这篇关于BigQuery字符串,用于在源中保留时区的时间戳的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!