Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
Contributor
 

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?

1 ACCEPTED SOLUTION

avatar

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

View solution in original post

2 REPLIES 2

avatar

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

avatar

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 ?