hiveql - Hive's unix_timestamp and from_unixtime functions

Question

I am under the impression that unix_timestamp and from_unixtime Hive functions are 'reverse' of each other.

When I try to convert timestamp string to seconds in Hive:

SELECT unix_timestamp('10-Jun-15 10.00.00.000000 AM', 'dd-MMM-yy hh.mm.ss.MS a');

I get 1418176800.

When I try to convert 1418176800 to timestamp string:

SELECT from_unixtime(1418176800, 'dd-MMM-yy hh.mm.ss.MS a');

I get 10-Dec-14 10.00.00.120 AM, which is obviously not equal to the original.

Can someone explain what's going on? Thanks.

Answer 1

From the language manual:

Convert time string with given pattern to Unix time stamp (in seconds) The result of this function is in seconds.

Your result changes with the milliseconds portion of the date, but the unix functions only support seconds. For example:

SELECT unix_timestamp('10-Jun-15 10.00.00 AM', 'dd-MMM-yy hh.mm.ss a');

1433930400

SELECT from_unixtime(1433930400, 'dd-MMM-yy hh.mm.ss a');

10-Jun-15 10.00.00 AM