Wednesday, March 11, 2015

Getting Date String from unix time stamp in HIVE Query

if You try to get the Date String from hive query as below, You will get an invalid date.

SELECT timestamp, from_unixtime(timestamp, 'yyyy-MM-dd HH:mm:ss') FROM TableName

1426109700733 > 47161-08-14 00:32:13
 
 from_unixtime expect the time in seconds and you have to use below query to get the date correct.

SELECT timestamp, from_unixtime(CAST(timestamp/1000 as BIGINT), 'yyyy-MM-dd HH:mm:ss') FROM TableName

1426109700733 > 2015-03-11 14:35:00

1 comment: