Uploaded image for project: 'IMPALA'
  1. IMPALA
  2. IMPALA-5203

from_utc_timestamp inconsistent how it handles daily savings time

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Resolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • 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

      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
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                loubershad Lou Bershad
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: