Snowflake中CONVERT_TIMEZONE来回转时区差1小时是bug吗？

这是预期行为，不是CONVERT_TIMEZONE函数的BUG，问题根源是历史时区的夏令时规则和你使用的时间类型逻辑不匹配。

偏差原因说明 #

• 1949年加拿大东部时区的夏令时切换规则为：4月24日凌晨0点整直接将时钟拨快1小时到1点，因此1949-04-24 00:00:00在Canada/Eastern时区属于不存在的无效时间。
• 你代码中出现偏差的逻辑如下：
  1. cast('1949-04-24' as timestamp)生成的是不带时区的TIMESTAMP_NTZ类型，Snowflake只会直接保留你输入的名义时间00:00:00，不会校验该时间在本地时区是否真实存在。
  2. 第一次调用convert_timezone时，你传入的是字符串格式的时间，Snowflake会先将该时间按源时区校验有效性，遇到不存在的时间会自动对齐到切换后的有效夏令时时间（即等价于1949-04-24 01:00:00 Canada/Eastern），对应的UTC时间就是1949-04-24 05:00:00，和你得到的date_utc结果一致。
  3. 第二次将UTC时间转回加拿大东部时区时，UTC 05:00:00对应的真实东部时间就是1点，因此date_local1返回01:00:00，和最初的名义时间0点出现偏差。

解决方案 #

如果要保证来回转换结果一致，请使用带时区的TIMESTAMP_TZ类型存储和传递时间，不要使用不带时区的TIMESTAMP_NTZ或者字符串格式传递时间，参考写法如下：

ALTER SESSION SET TIMEZONE = 'Canada/Eastern';
select
cast('1949-04-24' as timestamp_tz) as date_local_tz,
convert_timezone('UTC', date_local_tz) as date_utc,
convert_timezone('Canada/Eastern', convert_timezone('UTC', date_local_tz)) as date_local1_tz

以上写法转换前后的时间会完全匹配。

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