BigQuery按PST时区转换日期生成聚合表时统计结果错误如何解决

问题根因 #

你当前直接使用原始表的created_date（date类型）转换生成PST时区时间戳的逻辑存在缺陷：
created_date是无时间分量的固定日期，若该字段生成时采用的时区不是PST，会直接导致数据被划分到错误的PST日期分区，最终出现sum统计值异常的问题。

修复方案 #

所有时区转换逻辑需基于带时间分量的created_time字段计算，根据created_time存储的时区属性分为两种处理场景：

场景1：created_time存储的是UTC时区时间（最常见情况） #

直接将datetime类型的created_time先转换为UTC时间戳，再按PST时区截断到天即可，生成created_date_pst的语法如下：

DATE_TRUNC(TIMESTAMP(created_time, "UTC"), DAY, "America/Los_Angeles") AS created_date_pst

场景2：created_time存储的是PST时区时间 #

直接基于created_time做PST时区的天截断即可：

DATE_TRUNC(TIMESTAMP(created_time, "America/Los_Angeles"), DAY, "America/Los_Angeles") AS created_date_pst

完整聚合插入示例 #

INSERT INTO 你的聚合表名称 (created_date_pst, total_calls)
SELECT
  DATE_TRUNC(TIMESTAMP(created_time, "UTC"), DAY, "America/Los_Angeles") AS created_date_pst,
  SUM(calls) AS total_calls
FROM 你的原始表名称
-- 增量插入时可添加对应时间过滤条件，需和created_time的时区保持一致
WHERE created_time >= DATETIME("2024-01-01 00:00:00", "UTC")
GROUP BY 1

注意事项 #

• 时区参数统一使用America/Los_Angeles，不要使用PST缩写，可自动适配夏令时/冬令时切换，避免每年两次时区切换时的统计错误
• 不要直接使用原始表的created_date字段做时区转换，该字段无时间分量，无法匹配实际事件对应的PST日期
• 增量插入的时间过滤条件需和created_time的存储时区保持一致，避免漏数据或重复导入

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