Search code examples
hiveunix-timestamp

converting unixtimestamp to datetime failed to yield correct result in impala/hive

Apologize if this has been answered sometime and somewhere else. My query:

select from_unixtime(1501083119,"yyyy-MM-dd HH:mm:ss")

This returns correct result:

2017-07-26 15:31:59

My data is like: 1501081198789

as you can see, the value is quite different than the one in the test query, if I put this to the query: it returns NULL

Seems the last four digits are causing the problem, how do I get this working?

Thanks lots.

Solution

  • Since your epoch time is in milli seconds, if you modify your query as below you will get your desired output:

    select from_unixtime(cast('1501081198789'/1000 as bigint),"yyyy-MM-dd HH:mm:ss")

    2017-07-26 10:59:58