Details
-
Bug
-
Status: Resolved
-
Major
-
Resolution: Resolved
-
None
-
None
-
None
-
None
-
Impala Shell v2.6.0-cdh5.8.3 (9872875) built on Fri Dec 9 14:31:00 PST 2016
-
ghx-label-6
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:
- 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)
- 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
Attachments
Issue Links
- is related to
-
-
- Resolved
-
- relates to
-
IMPALA-3307 add support for IANA time zone database
-
- Resolved
-