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

to_utc_timestamp() doesn't consider daylight saving for all timezones

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: Impala 2.9.0, Impala 2.10.0, Impala 2.11.0
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Epic Color:
      ghx-label-3

      Description

      to_utc_timestamp does not consider daylight savings for all timezones shown in below example.

       Query-

      
      with t as (select current_timestamp() as rightnow),
      t1 as (select rightnow, 
      to_utc_timestamp(rightnow, 'PDT') as utc_ts
      from t)
      select t1.rightnow, t1.utc_ts, 
      from_utc_timestamp(utc_ts, 'PST') as pst_ts, 
      from_utc_timestamp(utc_ts, 'PDT') as pdt_ts, 
      from_utc_timestamp(utc_ts, 'MST') as mst_ts, 
      from_utc_timestamp(utc_ts, 'MDT') as mdt_ts, 
      from_utc_timestamp(utc_ts, 'CST') as cst_ts, 
      from_utc_timestamp(utc_ts, 'CDT') as cdt_ts, 
      from_utc_timestamp(utc_ts, 'EST') as est_ts, 
      from_utc_timestamp(utc_ts, 'EDT') as edt_ts, 
      from t1
      order by 1;
      
      

       

       

      impalad version 2.9.0-cdh5.12.1
      
      +-------------------------------+------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
      | rightnow | now_int | utc_ts | pst_ts | pdt_ts | mst_ts | mdt_ts | cst_ts | cdt_ts | est_ts | edt_ts | chicago_ts | az_ts | in_ts | in_knox_ts |
      +-------------------------------+------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
      | 2018-03-28 14:02:21.210465000 | 1522245741 | 2018-03-28 19:02:21.210465000 | 2018-03-28 12:02:21.210465000 | 2018-03-28 12:02:21.210465000 | 2018-03-28 12:02:21.210465000 | 2018-03-28 13:02:21.210465000 | 2018-03-28 14:02:21.210465000 | 2018-03-28 13:02:21.210465000 | 2018-03-28 14:02:21.210465000 | 2018-03-28 15:02:21.210465000 | 2018-03-28 14:02:21.210465000 | 2018-03-28 12:02:21.210465000 | 2018-03-28 15:02:21.210465000 | 2018-03-28 14:02:21.210465000 |
      +-------------------------------+------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
      
      
      impalad version 2.10.0-cdh5.13.x
      +-------------------------------+------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
      | rightnow | now_int | utc_ts | pst_ts | pdt_ts | mst_ts | mdt_ts | cst_ts | cdt_ts | est_ts | edt_ts | chicago_ts | az_ts | in_ts | in_knox_ts |
      +-------------------------------+------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
      | 2018-03-28 14:19:38.978262000 | 1522246778 | 2018-03-28 19:19:38.978262000 | 2018-03-28 12:19:38.978262000 | 2018-03-28 12:19:38.978262000 | 2018-03-28 12:19:38.978262000 | 2018-03-28 13:19:38.978262000 | 2018-03-28 14:19:38.978262000 | 2018-03-28 13:19:38.978262000 | 2018-03-28 14:19:38.978262000 | 2018-03-28 15:19:38.978262000 | 2018-03-28 14:19:38.978262000 | 2018-03-28 12:19:38.978262000 | 2018-03-28 15:19:38.978262000 | 2018-03-28 14:19:38.978262000 |
      +-------------------------------+------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
      
      impalad version 2.11.0-cdh5.14.x
      +-------------------------------+------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
      | rightnow | now_int | utc_ts | pst_ts | pdt_ts | mst_ts | mdt_ts | cst_ts | cdt_ts | est_ts | edt_ts | chicago_ts | az_ts | in_ts | in_knox_ts |
      +-------------------------------+------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
      | 2018-03-28 14:22:03.744552000 | 1522246923 | 2018-03-28 19:22:03.744552000 | 2018-03-28 12:22:03.744552000 | 2018-03-28 12:22:03.744552000 | 2018-03-28 12:22:03.744552000 | 2018-03-28 13:22:03.744552000 | 2018-03-28 14:22:03.744552000 | 2018-03-28 13:22:03.744552000 | 2018-03-28 14:22:03.744552000 | 2018-03-28 15:22:03.744552000 | 2018-03-28 14:22:03.744552000 | 2018-03-28 12:22:03.744552000 | 2018-03-28 15:22:03.744552000 | 2018-03-28 14:22:03.744552000 |
      +-------------------------------+------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+
      
       
      
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                mkempanna Mala Chikka Kempanna
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated: