Hive from_unixtime for milliseconds

Question

We have a timestamp epoch column (BIGINT) stored in Hive. We want to get Date 'yyyy-MM-dd' for this epoch. Problem is my epoch is in milliseconds e.g. 1409535303522. So select timestamp, from_unixtime(timestamp,'yyyy-MM-dd') gives wrong results for date as it expects epoch in seconds.

So i tried dividing it by 1000. But then it gets converted to Double and we can not apply function to it. Even CAST is not working when I try to Convert this double to Bigint.

Answer 1

Solved it by following query:

select timestamp, from_unixtime(CAST(timestamp/1000 as BIGINT), 'yyyy-MM-dd') from Hadoop_V1_Main_text_archieved limit 10;

Answer 2

In the original answer you'll get string, but if you'd like to get date you need to call extra cast with date:

select 
    timestamp, 
    cast(from_unixtime(CAST(timestamp/1000 as BIGINT), 'yyyy-MM-dd') as date) as date_col 
from Hadoop_V1_Main_text_archieved 
limit 10;

---

Docs for casting dates and timestamps. For converting string to date:

cast(string as date)
If the string is in the form 'YYYY-MM-DD', then a date value corresponding to that year/month/day is returned. If the string value does not match this formate, then NULL is returned.

Date type is available only from Hive > 0.12.0 as mentioned here:

DATE (Note: Only available starting with Hive 0.12.0)

Answer 3

The type should be double to ensure precision is not lost:

select from_unixtime(cast(1601256179170 as double)/1000.0, "yyyy-MM-dd hh:mm:ss.SSS") as event_timestamp

Answer 4

timestamp_ms is unixtime in milliseconds

SELECT from_unixtime(floor(CAST(timestamp_ms AS BIGINT)/1000), 'yyyy-MM-dd HH:mm:ss.SSS') as created_timestamp FROM table_name;