Hive FROM_UNIXTIME() with milliseconds

Question

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

Answer 1

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  )
 )