You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@impala.apache.org by "Lou Bershad (JIRA)" <ji...@apache.org> on 2017/04/12 21:59:41 UTC
[jira] [Created] (IMPALA-5203) from_utc_timestamp inconsistent how
it handles daily savings time
Lou Bershad created IMPALA-5203:
-----------------------------------
Summary: from_utc_timestamp inconsistent how it handles daily savings time
Key: IMPALA-5203
URL: https://issues.apache.org/jira/browse/IMPALA-5203
Project: IMPALA
Issue Type: Bug
Environment: Impala Shell v2.6.0-cdh5.8.3 (9872875) built on Fri Dec 9 14:31:00 PST 2016
Reporter: Lou Bershad
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.
{noformat}
[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
{noformat}
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).
{noformat}
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
{noformat}
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)