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

unix_timestamp() default timezone different from Hive or MySQL

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: Impala 1.4.1
    • Fix Version/s: Impala 2.2
    • Component/s: None
    • Labels:

      Description

      For the sake of demonstration, let's say I try to call unix_timestamp('2014-10-10 10:13:00') in both Hive and Impala. I get two different timestamps:

      • Hive: 1412928780
      • Impala: 1412935980

      Now if I try to look in a third (standard) place what these two timestamps correspond to, it seems to me like Hive is right. I looked in MySQL:

      mysql> select from_unixtime('1412928780') as 'Hive 1412928780'
                  , from_unixtime('1412935980') as 'Impala 1412935980';
      
      Hive 1412928780 Impala 1412935980
      2014-10-10 10:13:00 2014-10-10 12:13:00

      Interestingly, all these tests have been run in the same timezone (Europe/Amsterdam), which is 2 hours behind UTC time. It therefore feels as if Hive assumes the time string I give is in my Timezone, while Impala assumes that string is in the UTC Timezone.

      It would be nice if Impala would align its behavior with MySQL and Hive.

        Issue Links

          Activity

          Hide
          julienlehuen Julien Lehuen added a comment -

          This is related to IMPALA-97

          Show
          julienlehuen Julien Lehuen added a comment - This is related to IMPALA-97
          Hide
          knut_forkalsrud_impala_c4ca Knut Forkalsrud added a comment -

          Also, now() returns a timestamp in the local time zone while the documentation says it is a UTC timestamp.

          Show
          knut_forkalsrud_impala_c4ca Knut Forkalsrud added a comment - Also, now() returns a timestamp in the local time zone while the documentation says it is a UTC timestamp.
          Hide
          caseyc casey added a comment -

          Hi Knut, were you by chance looking at old documentation? The current docs say local time which is what we want for MySQL compatibility.

          http://www.cloudera.com/content/cloudera/en/documentation/cloudera-impala/latest/topics/impala_datetime_functions.html
          http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_now

          Show
          caseyc casey added a comment - Hi Knut, were you by chance looking at old documentation? The current docs say local time which is what we want for MySQL compatibility. http://www.cloudera.com/content/cloudera/en/documentation/cloudera-impala/latest/topics/impala_datetime_functions.html http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_now
          Hide
          caseyc casey added a comment -

          In 2.2 there will be a flag -use_local_tz_for_unix_timestamp_conversions to enable Hive compatibility.

          Show
          caseyc casey added a comment - In 2.2 there will be a flag -use_local_tz_for_unix_timestamp_conversions to enable Hive compatibility.
          Hide
          caseyc casey added a comment -

          commit e65ab98f8fb6770ed948b4b7a027cc1c835c8b94

          Show
          caseyc casey added a comment - commit e65ab98f8fb6770ed948b4b7a027cc1c835c8b94

            People

            • Assignee:
              caseyc casey
              Reporter:
              julienlehuen Julien Lehuen
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development