Why not from_unixtime() function handles an unix timestamp in milliseconds?

[#1]

Hi,

A column in my table has an unix time in milliseconds. When I am trying to use from_unixtime() it is returning me null. In the documentation its mentioned that from_unixtime() handles only unix time in seconds. Any specific problem handling milliseconds? 

select from_unixtime(1513895588243,"yyyy-MM-dd HH:mm:ss.SSSSSS");

Result> from_unixtime(1513895588243, 'yyyy-mm-dd hh:mm:ss.ssssss')
 NULL

I am expecting '2017-12-21 22:33:08.243000000'

I wrote below query which would give me the result as expected above,

select 1513895588243, cast(concat(cast(from_unixtime(CAST(1513895588243/1000 as BIGINT), 'yyyy-MM-dd HH:mm:ss') as String),'.',substr(cast(1513895588243 as String), 11, 3)) as timestamp);

Result:
1513895588243      2017-12-21 22:33:08.243000000

But, the above query is not an efficient way. Is there any efficient workaround for this?

[#2]

Hi,

A column in my table has an unix time in milliseconds. When I am trying to use from_unixtime() it is returning me null. In the documentation its mentioned that from_unixtime() handles only unix time in seconds. Any specific problem handling milliseconds? 

select from_unixtime(1513895588243,"yyyy-MM-dd HH:mm:ss.SSSSSS");

Result> from_unixtime(1513895588243, 'yyyy-mm-dd hh:mm:ss.ssssss')
 NULL

I am expecting '2017-12-21 22:33:08.243000000'

I wrote below query which would give me the result as expected above,

select 1513895588243, cast(concat(cast(from_unixtime(CAST(1513895588243/1000 as BIGINT), 'yyyy-MM-dd HH:mm:ss') as String),'.',substr(cast(1513895588243 as String), 11, 3)) as timestamp);

Result:
1513895588243      2017-12-21 22:33:08.243000000

But, the above query is not an efficient way. Is there any efficient workaround for this?

[#3 · Accepted solution]

I believe the original reason that it took seconds was for compatibility with Mysql's similarly-named function.

You can convert a millisecond unix timestamp into an Impala timestamp more efficiently using integer division/mod and the "interval" operator as follows:

[localhost:21000] > select cast(1513895588243 div 1000 as timestamp) + interval (1513895588243 % 1000) milliseconds;
+------------------------------------------------------------------------------------------+
| cast(1513895588243 div 1000 as timestamp) + interval (1513895588243 % 1000) milliseconds |
+------------------------------------------------------------------------------------------+
| 2017-12-21 22:33:08.243000000                                                            |
+------------------------------------------------------------------------------------------+
Fetched 1 row(s) in 0.01s

[#4]

The same can be peformed in hive using concat_ws('.',from_unixtime(cast(epochmillis/1000 as BIGINT),'yyyy-MM-dd HH:mm:ss'),cast(floor(epochmillis % 1000) as STRING)) to get the timestamp with milliseconds.

Is this efficient way of doing it ?