BigQuery中将America/Chicago时区时间转换为UTC datetime的方法

核心逻辑 #

自动Schema检测将美国中部（America/Chicago）时区的本地时间错误标记为UTC时区的Timestamp，导致时间值偏移了5/6小时（随夏令时变动）。要得到正确的UTC时间，只需先剥离错误的UTC时区标记，再给时间值赋予正确的源时区后转Timestamp即可。

方案1：已有表新增正确UTC列 #

执行SQL新增列并批量更新：

-- 新增存储正确UTC时间的列
ALTER TABLE `你的数据集.你的表名`
ADD COLUMN correct_utc_timestamp TIMESTAMP;

-- 批量计算更新正确值
UPDATE `你的数据集.你的表名`
SET correct_utc_timestamp = TIMESTAMP(
  DATETIME(自动识别的错误Timestamp列名),
  "America/Chicago"
)
WHERE 1=1;

注意：不要使用固定加减小时的方式处理偏移，America/Chicago时区名会自动适配夏令时规则，避免一年两次的1小时偏移错误。

方案2：新建外部表时从源头规避错误 #

如果还在建表阶段，建议关闭自动Schema检测，手动指定时间字段类型为DATETIME，还可以通过生成列自动计算正确UTC时间：

CREATE EXTERNAL TABLE `你的数据集.你的表名` (
  -- 其他字段按需定义
  chicago_local_datetime DATETIME,
  -- 自动生成正确UTC时间的生成列，无需手动维护
  correct_utc_timestamp TIMESTAMP AS (TIMESTAMP(chicago_local_datetime, "America/Chicago"))
)
OPTIONS (
  format = 'CSV', -- 替换为你的源文件格式，比如PARQUET等
  uris = ['gs://你的GCS桶路径/*']
);

正确性验证示例 #

你可以用测试值验证逻辑正确性：

• 源数据实际中部时间：2024-06-01 12:00:00（夏令时期间，比UTC晚5小时）
• 自动识别的错误Timestamp值：TIMESTAMP("2024-06-01 12:00:00 UTC")
• 用上述方法计算得到的正确UTC值：2024-06-01 17:00:00 UTC，符合预期。

内容的提问来源于stack exchange，提问作者MT467