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.

If you change the format to 'yyyy-MM-dd HH:mm:ss.SSS'? – LiMuBei Jan 14 '15 at 16:34 — LiMuBei, Jan 14 '15 at 16:34

score 35 · Accepted Answer · edited Oct 18 '16 at 20:24

35

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;

edited Oct 18 '16 at 20:24

Cristian

198,401
62
356
264

answered Jan 15 '15 at 10:48

Sourabh Potnis

1,431
1
17
26

this does not work.. it gives an error - No matching method for class org.apache.hadoop.hive.ql.udf.UDFFromUnixTime with (double, string). – Arnab Jul 30 '16 at 05:12

score 4 · Answer 2 · edited Nov 03 '20 at 11:17

4

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

edited Nov 03 '20 at 11:17

Elletlar

3,136
7
32
38

answered Nov 03 '20 at 06:17

NicolasLi

97
1
3

anjaneyulu yarrapothu · Answer 3 · 2016-09-01T12:35:43.310

3

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;

edited Sep 01 '16 at 12:35

answered Sep 01 '16 at 12:16

anjaneyulu yarrapothu

59
4

score 3 · Answer 4 · answered Feb 21 '17 at 07:42

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)

Hive from_unixtime for milliseconds

4 Answers4

Linked