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

unix_timestamp() returns timestamp relative to local timezone epoch

    Details

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

      Description

      Impala is returning timestamps relative to the local timezone epoch, which is inconsistent with Hive's behaviour. See also IMPALA-97.

      Some examples:

      unix_timestamp()
      

      Expected output is the current date/time relative to UTC epoch.

      At 7/11/2014 7:38:31 PM PDT, Impala returns 1405107511, which would be current unix time relative to the local timezone's epoch (not UTC).
      At 7/11/2014 7:38:31 PM PDT, Hive returns 1405132711: this uses UTC epoch.

      cast(cast(unix_timestamp() as timestamp) as string)
      # note: Hive needs "* 1000", see HIVE-3454
      cast(cast(unix_timestamp() * 1000 as timestamp) as string)
      

      At 7:25pm PST, Jul 14, 2014: Impala returns "2014-07-15 02:25:40"
      At 7:31pm PST, Jul 14, 2014: Hive returns "2014-07-14 19:31:12"

      unix_timestamp('2013-01-01 19:13:38.000')
      

      Expected output is the timestamp value of this string relative to UTC epoch.

      Impala returns 1357067618: this represents the date/time value assuming the same epoch as the date/time string representation (ie, a UTC string will get you a UTC epoch, a localtime string will get you localtime epoch).

      Hive returns 1357096418: epoch is UTC and string is assumed to be local timezone. Can specify an alternative timezone by using a SimpleDateFormat string.

      Edit: typo in sample output

        Issue Links

          Activity

          Hide
          benwhite Ben White added a comment -

          Just discovered that unix_timestamp() with no arguments does return the current time as a UTC timestamp if env var TZ=UTC. Other invocations still assume UTC strings.

          Show
          benwhite Ben White added a comment - Just discovered that unix_timestamp() with no arguments does return the current time as a UTC timestamp if env var TZ=UTC. Other invocations still assume UTC strings.
          Hide
          caseyc casey added a comment -

          I'll use IMPALA-97

          Show
          caseyc casey added a comment - I'll use IMPALA-97

            People

            • Assignee:
              caseyc casey
              Reporter:
              benwhite Ben White
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development