CODEWITHSUNDEEP
hive
logic4funi
logic4funi

Reputation: 65

Hive FROM_UNIXTIME() with milliseconds

I have seen enough posts where we divide by 1000 or cast to convert from Milliseconds epoch time to Timestamp. I would like to know how can we retain the Milliseconds piece too in the timestamp.

1440478800123 The last 3 bytes are milliseconds. How do i convert this to something like YYYYMMDDHHMMSS.sss

I need to capture the millisecond portion also in the converted timestamp

Thanks

Upvotes: 4

Views: 13023

Answers (2)

Mark Nettle
Mark Nettle

Reputation: 59

select cast(epoch_ms as timestamp)

actually works, because when casting to a timestamp (as opposed to using from_unixtime()), Hive seems to assume an int or bigint is milliseconds. A floating point type is treated as seconds. That is undocumented as far as I can see, and possibly a bug. I wanted a string which includes the timezone (which can be important - particularly if the server changes to summer/daylight savings time), and wanted to be explicit about the conversion in case the cast functionality changes. So this gives an ISO 8601 date (adjust format string as needed for another format)

select from_unixtime(
   floor(  epoch_ms / 1000   )
 , printf( 'yyyy-MM-dd HH:mm:ss.%03dZ',  epoch_ms % 1000  )
 )

Upvotes: 5

Kishore
Kishore

Reputation: 5891

create a hive udf in java

package com.kishore.hiveudf;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.hive.ql.udf.UDFType;

@UDFType(stateful = true)
public class TimestampToDateUDF extends UDF {
    String dateFormatted;
    public String evaluate(long timestamp) {
        Date date = new Date(timestamp);
        DateFormat formatter = new SimpleDateFormat("YYYYMMDDHHmmss:SSS");
        dateFormatted = formatter.format(date);
        return dateFormatted;
    }
}

export as TimestampToDateUDF.jar

hive> ADD JAR /home/kishore/TimestampToDate.jar; 
hive> create TEMPORARY FUNCTION toDate AS 'com.kishore.hiveudf.TimestampToDateUDF' ;

output

select * from tableA;                                                           
OK
1440753288123
Time taken: 0.071 seconds, Fetched: 1 row(s)
hive> select toDate(timestamp) from tableA;
OK
201508240144448:123
Time taken: 0.08 seconds, Fetched: 1 row(s)

Upvotes: 3

Related Questions