difference between 2 timestamp data with nanoseconds in hive

Question

I have 2 columns with timestamp data upto nanoseconds which is coming from a transaction log file. I want to find the difference in time upto nanoseconds. I have tried to directly subtract the columns data by converting them into timestamp but getting multiple errors while doing so. Is there any way to do it in Hive.

Request_Time Response_Time 2018-11-08 12:24:24.123456345 2018-11-09 12:24:24.123556567 2018-11-08 23:59:59.234123678 2018-11-09 00:00:00.342567456 2018-11-08 12:24:24.678345345 2018-11-09 01:02:03.688456678 2018-11-08 12:24:24.789456234 2018-11-08 12:24:24.799455567

I want to find the difference which is (Response_Time - Request_Time). Any help is appreciated.

@GaurangShah: No matching method for class org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPMinus with (timestamp, timestamp) . Also i don't know, even if there is some method for this, what will be the return value.Will it be seconds, milliseconds, nanoseconds etc. — saurabh verma, Nov 08 '18 at 16:47

score 1 · Answer 1 · answered Nov 08 '18 at 20:18

I don't think there is a direct method to do this,

What you can do is convert the timestamp to unix timestamp and then get the difference, However that will give you difference till seconds only, not till nanoseconds.

However, following will.

SELECT (unix_timestamp("2018-11-09 12:24:25.123556567")+cast(split("2018-11-09 12:24:25.123556568","\\.")[1]*0.0000000010 as decimal (12, 12)))
-
(unix_timestamp("2018-11-09 12:24:25.123556567")+cast(split("2018-11-09 12:24:25.123556567","\\.")[1]*0.0000000010 as decimal (12, 12)) )
from temp.test_time

The difference will be in following format

unix_timestamp.nano_seconds

For table,

SELECT *,(unix_timestamp(time1)+cast(split(time1,"\\.")[1]*0.0000000010 as decimal (12, 12))) - 
(unix_timestamp(time)+cast(split(time,"\\.")[1]*0.0000000010 as decimal (12, 12)))
from temp.test_time

difference between 2 timestamp data with nanoseconds in hive

1 Answers1