Search code examples
sqlhivehiveqlhortonworks-data-platformepoch

Hive - Convert epoch time (ms - 13 digit) to timestamp till milliseconds in hive sql

I need to get completed timestamp from epoch timestamp in hive query.

For example 
epoch time      --- 1606407266850
timestamp value --- 2020-11-26 21:44:26 
expected value  --- 2020-11-26 21:44:26.850

My method in hive:

Select timestamp(concat(from_unixtime(CAST(lastModifiedOn AS BIGINT),'yyyy-MM-dd HH:mm:ss'),".",CAST(lastModifiedOn AS BIGINT)%1000),'yyyy-MM-dd HH:mm:ss.SSS')
from db.table1

My question: Do we have better approach to get timestamp value till millisecond? we can easily get value till second.

Solution 
  • select cast(lastModifiedOn/1000 as timestamp);

    Can't specify the precision but will keep the timestamp up to the highest precision