BigQuery中FOR SYSTEM_TIME AS OF子句无指定时区时间戳未按UTC解析
根据Google BigQuery文档说明:
时间戳字面量可携带数字后缀用以指定时区:
TIMESTAMP '2014-09-27 12:30:00.45-08'
若未携带此时区后缀,将默认使用UTC时区。
我在其他场景下观测到的行为与上述文档一致,但在FOR SYSTEM_TIME AS OF子句的上下文场景中除外。
例如执行如下查询:SELECT * FROM doesnt.matter FOR SYSTEM_TIME AS OF TIMESTAMP'2020-12-16 17:59:14.445596'
会收到如下报错信息:
表'doesntmatter'的'FOR SYSTEM_TIME AS OF'表达式计算得到的TIMESTAMP值为未来时间:2020-12-17 01:59:14.445596 UTC。
值得注意的是报错信息中的时间戳标注为UTC,但与查询中传入的时间值明显不符(按上述文档说明该值本应按UTC解析)。
实际上,即使为上述时间戳字面量添加-08作为时区后缀,也会收到完全相同的报错。该8小时偏移量似乎与数据集所属区域无关(无论使用US还是EU区域的数据集,报错完全一致)。
若为时间戳添加显式的UTC时区标识,则报错符合预期:SELECT * FROM doesnt.matter FOR SYSTEM_TIME AS OF TIMESTAMP'2020-12-16 17:59:14.445596 UTC'
未找到:数据集[...]:doesnt 不存在于US区域。
(注:上述测试使用的数据集和表在任意时间点均未创建。)
请问该问题是我存在操作错误(或遗漏了文档中的相关说明),还是BigQuery本身的产品问题?
这是BigQuery的已知产品逻辑偏差,不属于你的操作错误或文档遗漏。
根因解释
- 公开文档规定:不带时区后缀的时间戳字面量默认解析为UTC。但
FOR SYSTEM_TIME AS OF子句的解析逻辑存在偏差,会按照当前会话的时区解析不带时区的时间戳。 - 你当前的BigQuery会话默认时区为UTC-8(太平洋时间),因此不带时区的
2020-12-16 17:59:14.445596被识别为UTC-8时区的对应时间,转换为UTC标准时间后增加8小时,得到报错中的2020-12-17 01:59:14.445596 UTC。 - 你提到“无论使用US还是EU区域的数据集,报错完全一致”符合逻辑:偏移量由会话时区决定,和数据集所属区域无关。
- 你提到“添加
-08时区后缀后报错完全一致”符合逻辑:显式指定-08后缀时,时间戳同样被解析为UTC-8时区的对应值,转换为UTC后的结果和不带后缀的解析结果完全相同。 - 你提到“添加显式UTC标识后报错符合预期”也验证了这一点:显式指定UTC后,子句会正确识别时区,不再走会话时区的解析逻辑。
临时解决方案
执行以下语句将当前会话时区设置为UTC,即可让时间戳解析符合文档预期:SET @@time_zone = 'UTC';
后续说明
该问题已经由Google官方登记在问题工单中,后续要么会调整FOR SYSTEM_TIME AS OF的解析逻辑对齐文档要求,要么会更新文档补充该子句的特殊解析规则。
基于官方文档给出的FOR SYSTEM_TIME AS OF示例,在实际创建表的场景下也可复现相同问题:
-- 创建books表 CREATE OR REPLACE TABLE test.books AS SELECT 'Hamlet' title, 'William Shakespeare' author; -- 表替换前获取当前时间戳 SELECT current_timestamp; -- 以不同的schema(标题和发行日期)替换该表 CREATE OR REPLACE TABLE test.books AS SELECT 'Hamlet' title, DATE '1603-01-01' release_date; -- 手动复制粘贴上述SELECT current_timestamp查询返回的不带时区的时间戳: SELECT * FROM doesnt.matter FOR SYSTEM_TIME AS OF TIMESTAMP'<<paste here>>';
内容的提问来源于stack exchange,提问作者Markus Winand

