CODEWITHSUNDEEP
hivetimestamphiveqlsql-timestamp
saurabh verma
saurabh verma

Reputation: 19

difference between 2 timestamp data with nanoseconds in hive

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.

Upvotes: 1

Views: 847

Answers (1)

Gaurang Shah
Gaurang Shah

Reputation: 12910

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

Upvotes: 1

Related Questions