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

Impala returns "1970-01-01 00:00:00" in from_unixtime(0) , Hive returns "1970-01-01 09:00:00" in Japan

    Details

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

      Description

      Incompatibility between Impala and Hive in Japan( or other timezones except for UTC):

      Impala:

      select from_unixtime(0);
      Query: select from_unixtime(0)
      Query finished, fetching results ...
      1970-01-01 00:00:00
      Returned 1 row(s) in 0.01s
      

      Hive:

      select from_unixtime(0) from tmp;         
      <snip>
      OK
      1970-01-01 09:00:00
      

      This is because Hive returns as current system time zone(GMT+9), but Impala returns as UTC.

        Issue Links

          Activity

          Hide
          nong_impala_60e1 Nong Li added a comment -

          Skye, can you look into this? I think we've documented this as a known hive incompatibility because the hive implementation differs from their docs (we implemented what the doc specified).

          Show
          nong_impala_60e1 Nong Li added a comment - Skye, can you look into this? I think we've documented this as a known hive incompatibility because the hive implementation differs from their docs (we implemented what the doc specified).
          Hide
          showyou Yuki Yoi added a comment -

          Sorry, I found that this is incompatibility in the docs.It is not a bug.
          But it troubles a lot of people except for UTC users.
          So I strongly wish that this incompatibility is removed.

          Show
          showyou Yuki Yoi added a comment - Sorry, I found that this is incompatibility in the docs.It is not a bug. But it troubles a lot of people except for UTC users. So I strongly wish that this incompatibility is removed.
          Hide
          skye Skye Wanderman-Milne added a comment -

          How do we want to handle this? I think automatically converting timestamp results to the local timezone can be confusing (e.g., WHERE conditionals would still have to be expressed in UTC). Right now you can convert UTC output to a specific timezone using from_utc_timestamp(). We could also create a timezone query option that, if set, converts results – at least then it's more explicit than doing the conversion by default.

          Show
          skye Skye Wanderman-Milne added a comment - How do we want to handle this? I think automatically converting timestamp results to the local timezone can be confusing (e.g., WHERE conditionals would still have to be expressed in UTC). Right now you can convert UTC output to a specific timezone using from_utc_timestamp(). We could also create a timezone query option that, if set, converts results – at least then it's more explicit than doing the conversion by default.
          Hide
          showyou Yuki Yoi added a comment -

          Skye, your thinks seems true. But I conscious of differences between Hive.
          I think that using from_utc_timestamp() every time is crazy.
          At least, I want to a option "use local timezone".

          Show
          showyou Yuki Yoi added a comment - Skye, your thinks seems true. But I conscious of differences between Hive. I think that using from_utc_timestamp() every time is crazy. At least, I want to a option "use local timezone".
          Hide
          marcelk Marcel Kornacker added a comment -

          I agree with both Show (make it easy to use the local time zone) and Skye (but we shouldn't make that the default because it's probably confusing/unexpected).

          I think a good approach would be a query option that enables the local time zone behavior.

          Show
          marcelk Marcel Kornacker added a comment - I agree with both Show (make it easy to use the local time zone) and Skye (but we shouldn't make that the default because it's probably confusing/unexpected). I think a good approach would be a query option that enables the local time zone behavior.
          Hide
          benwhite Ben White added a comment -

          Something else to consider: unix_timestamp() (the inverse of from_unixtime) assumes the input string is in localtime in Hive, but UTC in Impala. I'll open a separate issue for that if others don't agree it belongs here, but the solution will need to work for conversion in both directions.

          Show
          benwhite Ben White added a comment - Something else to consider: unix_timestamp() (the inverse of from_unixtime) assumes the input string is in localtime in Hive, but UTC in Impala. I'll open a separate issue for that if others don't agree it belongs here, but the solution will need to work for conversion in both directions.
          Hide
          julienlehuen Julien Lehuen added a comment -

          Filed IMPALA-1435 for the same issue with regards to unix_timestamp()

          Show
          julienlehuen Julien Lehuen added a comment - Filed IMPALA-1435 for the same issue with regards to unix_timestamp()
          Hide
          caseyc casey added a comment -

          It seems this really is an Impala bug. As far as I can tell from_unixtime() is a MySQL specific function (neither Postgres nor Oracle provide it), and it returns the same value that Hive does. I'll update Impala to match.

          Show
          caseyc casey added a comment - It seems this really is an Impala bug. As far as I can tell from_unixtime() is a MySQL specific function (neither Postgres nor Oracle provide it), and it returns the same value that Hive does. I'll update Impala to match.
          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
          Hide
          showyou Yuki Yoi added a comment -

          Casey, great job!

          Show
          showyou Yuki Yoi added a comment - Casey, great job!

            People

            • Assignee:
              caseyc casey
              Reporter:
              showyou Yuki Yoi
            • Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development