CODEWITHSUNDEEP
hiveunix-timestamp
PasLeChoix
PasLeChoix

Reputation: 311

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.

Upvotes: 2

Views: 5710

Answers (1)

Prabhat Ratnala
Prabhat Ratnala

Reputation: 705

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

Upvotes: 3

Related Questions