BigQuery解析日期时出现时区转换错误及夏令时处理问题

问题1：SQL语句运行错误的修复方案 #

你当前语句报错的核心原因是格式化符号与输入的年份格式不匹配：

• %Y 对应4位完整年份（例如2021），但你的输入中年份是2位的21，需要替换为适配2位年份的格式化符%y

修复后的正确语句如下：

SELECT
SAFE.PARSE_TIMESTAMP('%m/%d/%y %I:%M %p', '9/6/21 7:22 AM',"US/Central")

运行后会得到正确的时间戳结果：2021-09-06 12:22:00 UTC

问题2：BigQuery夏令时的正确处理方式 #

BigQuery内置的时区数据库已经默认集成了全球各地区的夏令时规则，处理夏令时相关场景可遵循以下规范：

• 所有涉及多时区/夏令时的时间存储，优先存储UTC时区的TIMESTAMP类型，不要直接存储带时区的字符串或者DATETIME类型，避免后续转换出现歧义
• 解析带有时区的本地时间字符串时，必须显式指定对应时区，不要依赖BigQuery的默认时区配置，你上述语句中显式传入"US/Central"就是符合规范的做法
• 处理夏令时切换导致的特殊时间场景：
  • 夏令时开始时会出现1小时的不存在时间（例如北美中部时区每年3月某个周日2点会直接跳到3点，2:30这个时间不存在），使用SAFE.PARSE_TIMESTAMP会返回NULL而非报错，如果需要自定义兜底值可搭配IFNULL使用
  • 夏令时结束时会出现1小时的重复时间（例如11月某个周日2点会跳回1点，1:30会出现两次），BigQuery默认会解析为夏令时结束前的对应时间，如果你需要指定为第二个重复时间，可以额外添加1小时偏移处理
• 如果需要判断某个时间是否处于夏令时时段，可以通过FORMAT_TIMESTAMP提取时区缩写判断（例如US/Central时区夏令时缩写为CDT，标准时为CST）

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