Impala is returning timestamps relative to the local timezone epoch, which is inconsistent with Hive's behaviour. See also
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"
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
IMPALA-97 Impala returns "1970-01-01 00:00:00" in from_unixtime(0) , Hive returns "1970-01-01 09:00:00" in Japan