Reply
Explorer
Posts: 12
Registered: ‎04-26-2017
Accepted Solution

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

 

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?

Highlighted
Cloudera Employee
Posts: 290
Registered: ‎07-29-2015

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

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
Announcements