[IMPALA-1086] unix_timestamp() returns timestamp relative to local timezone epoch

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: Impala 1.3.1
• Fix Version/s: Impala 2.2
• Assignee: casey
• Reporter: Ben White
• Created: 15/Jul/14 02:58
• Updated: 20/Dec/15 00:05
• Resolved: 21/Feb/15 03:12

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

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