Search code examples
hivetimestamphiveqlunix-timestamp

epoch with milliseconds to timestamp with milliseconds conversion in Hive

How can I convert unix epoch with milliseconds to timestamp with milliseconds In Hive? Neither cast() nor from_unixtime() function is working to get the timestamp with milliseconds.

I tried .SSS but the function just increases the year and doesn't take it as a part of millisecond.

scala> spark.sql("select from_unixtime(1598632101000, 'yyyy-MM-dd hh:mm:ss.SSS')").show(false)
+-----------------------------------------------------+
|from_unixtime(1598632101000, yyyy-MM-dd hh:mm:ss.SSS)|
+-----------------------------------------------------+
|52628-08-20 02:00:00.000                             |
+-----------------------------------------------------+
Solution

  • I think you can just cast():

    select cast(1598632101000 / 1000.0 as timestamp)

    Note that this produces a timestamp datatype rather than a string, as in from_unixtime().