Geek Answers Handbook

Hive unix_timestamp and from_unixtime functions

I get the impression that unix_timestamp and from_unixtime . Hive functions are "inverse" to each other.

When I try to convert a timestamp string to a few 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 a 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 is happening? Thanks.

+6
source share
2 answers

From the language manual:

Converting a time string with a given pattern to a Unix timestamp (in seconds) The result of this function is in seconds.

Your result changes with the millisecond part of the date, but unix functions only support seconds. For instance:

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

+12
source

Do not use from unix_timestamp, as in the second request. In addition, your statement has formatting that gives the result when DEC is used instead of 12. See Dd dd-MM-yy . Do not specify a format, and it should work. See examples below.

However, you are correct that from_unixtime() and unix_timestamp() are used to convert back and forth from a time line.

 select unix_timestamp('2015-04-09 03:04:26') from dual;

Results "1428566666"

 select from_unixtime(1428566666) from dual;

Results in the "2015-04-09 03:04:26"

+2
source

More articles:


All Articles