Hive: extracting milliseconds from interval_day_time?

Question

I'd like to get millisecond precision from some datetime deltas I have. There's no millisecond() function that I see for Hive.

Consider this:

with t as (
    select (CAST(1481652239798 AS TIMESTAMP) - CAST(1481652228576 as timestamp)) 
    as delta
) 
select delta from t;

0 00:00:11.222000000

I can work with that, if I could convert that output into a string and extract the part following the period.

with t as (
    select (CAST(1481652239798 AS TIMESTAMP) - CAST(1481652228576 as timestamp)) 
    as delta
) 
select instr(delta, '.') from t

11 -- correct index of '.'

So instr() treats delta as a string, but I can't substring it:

with t as (
    select (CAST(1481652239798 AS TIMESTAMP) - CAST(1481652228576 as timestamp)) 
    as delta
) 
select substr(delta, 11) from t; -- directly supplying instr() leads to a different bug with parsing the query syntax

No matching method for class org.apache.hadoop.hive.ql.udf.UDFSubstr with (interval_day_time, int)

Any workarounds?

Answer 1

If you cast the timestamp object to double it preserves the milliseconds part.

So try the following:

with t as (
    select CAST(1481652239798 AS TIMESTAMP) as ts1,  
           CAST(1481652228576 as timestamp) as ts2             
) 
select ts1,
       ts2,
       (ts1-ts2) as delta, 
       floor((CAST(ts1 AS double)-CAST(ts2 as double))*1000) as delta_ms
from t