[IMPALA-5203] from_utc_timestamp inconsistent how it handles daily savings time

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Resolved
• Environment: Impala Shell v2.6.0-cdh5.8.3 (9872875) built on Fri Dec 9 14:31:00 PST 2016
• Epic Color: ghx-label-6
• Assignee: Unassigned
• Reporter: Lou Bershad
• Created: 12/Apr/17 21:58
• Updated: 09/Jul/18 17:01
• Resolved: 09/Jul/18 17:01

Description

from_utc_timestamp(ts, EDT) as the timezone adjusts the time correctly whether or not the timestamp being translated was during daylight savings time. That is, it adjusts 4 hours for dates during daylight savings time and 5 hours for dates during standard time. from_utc_timetamp(ts, EST) always adjusts by 5 hours.

In 2017, daylight savings time started on March 12th. This query shows that from_utc_timestamp using EDT adjusts 5 hours on March 11th and 4 hours on March 13th. When using EST, it adjusts 5 hours no matter what.

[i-pvd1c1mgr-vip.vldb-bo.secureworkslab.com:21001] > select 'EST', from_utc_timestamp('2017-03-11', 'EST'), from_utc_timestamp('2017-03-13', 'EST') ;
Query: select 'EST', from_utc_timestamp('2017-03-11', 'EST'), from_utc_timestamp('2017-03-13', 'EST')
+-------+-----------------------------------------+-----------------------------------------+
| 'est' | from_utc_timestamp('2017-03-11', 'est') | from_utc_timestamp('2017-03-13', 'est') |
+-------+-----------------------------------------+-----------------------------------------+
| EST   | 2017-03-10 19:00:00                     | 2017-03-12 19:00:00                     |
+-------+-----------------------------------------+-----------------------------------------+
Fetched 1 row(s) in 0.01s
[i-pvd1c1mgr-vip.vldb-bo.secureworkslab.com:21001] > select 'EST', from_utc_timestamp('2017-03-11', 'EDT'), from_utc_timestamp('2017-03-13', 'EDT') ;
Query: select 'EST', from_utc_timestamp('2017-03-11', 'EDT'), from_utc_timestamp('2017-03-13', 'EDT')
+-------+-----------------------------------------+-----------------------------------------+
| 'est' | from_utc_timestamp('2017-03-11', 'edt') | from_utc_timestamp('2017-03-13', 'edt') |
+-------+-----------------------------------------+-----------------------------------------+
| EST   | 2017-03-10 19:00:00                     | 2017-03-12 20:00:00                     |
+-------+-----------------------------------------+-----------------------------------------+
Fetched 1 row(s) in 0.01s

The inconsistency could be fixed either by:

1. EST acts the same as EDT and adjusts the timestamp based on whether the timestamp is during daylight savings time. (I feel quite strongly that this would be the correct choice)
2. EDT always adjusts by 4 hours

Note: The same dichotomy exists in other US timezones: PST/PDT, CST/CDT and MST/MDT. The dichotomy does not exist in France (CET/CEST).

Query: select tz, from_utc_timestamp('2017-03-01', tz), from_utc_timestamp('2017-05-01', tz) from (
    select 'EST' tz union all
    select 'EDT' tz union all
    select 'CST' tz union all
    select 'CDT' tz union all
    select 'PST' tz union all
    select 'PDT' tz union all
    select 'CET' tz union all
    select 'CEST' tz
) x
+------+--------------------------------------+--------------------------------------+
| tz   | from_utc_timestamp('2017-03-01', tz) | from_utc_timestamp('2017-05-01', tz) |
+------+--------------------------------------+--------------------------------------+
| EST  | 2017-02-28 19:00:00                  | 2017-04-30 19:00:00                  |
| EDT  | 2017-02-28 19:00:00                  | 2017-04-30 20:00:00                  |
| CST  | 2017-02-28 18:00:00                  | 2017-04-30 19:00:00                  |
| CDT  | 2017-02-28 18:00:00                  | 2017-04-30 19:00:00                  |
| PST  | 2017-02-28 16:00:00                  | 2017-04-30 17:00:00                  |
| PDT  | 2017-02-28 16:00:00                  | 2017-04-30 17:00:00                  |
| CET  | 2017-03-01 01:00:00                  | 2017-05-01 02:00:00                  |
| CEST | 2017-03-01 01:00:00                  | 2017-05-01 02:00:00                  |
+------+--------------------------------------+--------------------------------------+
Fetched 8 row(s) in 0.02s

Issue Links

• is related to: IMPALA-2546 Invalid time zones are ignored but should be rejected
• relates to: IMPALA-3307 add support for IANA time zone database