Snowflake CONVERT_TIMEZONE bug?

user_17492344 0
Viewed 203

In Snowflake, when converting some local dates to UTC and then back to local timezone using CONVERT_TIMEZONE function, the end result is off by one hour. For example:

ALTER SESSION SET TIMEZONE = 'Canada/Eastern';
select
cast('1949-04-24' as timestamp) as date_local -- because TIMEZONE = 'Canada/Eastern'
, convert_timezone('Canada/Eastern', 'UTC', '1949-04-24') as date_utc
, convert_timezone('UTC', 'Canada/Eastern', convert_timezone('Canada/Eastern', 'UTC', '1949-04-24')) as date_local1

The results are:

DATE_LOCAL DATE_UTC DATE_LOCAL1
1949-04-24 00:00:00.0 1949-04-24 05:00:00.0 1949-04-24 01:00:00.0

I expect both DATE_LOCAL and DATE_LOCAL1 to be identical, however DATE_LOCAL1 is one hour off. Is this expected? Am I missing something or there is a bug in the CONVERT_TIMEZONE function?

snowflake-cloud-data-platform
1 Answers

This is not a bug. This is about Daylight Saving Time (DST). In 1949, DST started on Sunday, 24 April, 00:00. So when you convert it to UTC, It becomes 1949-04-24 05:00:00.0. When you convert it back, it becomes 01:00 because of DST, because it's the time that the clocks were turned forward one hour.

user_125509650
Related