CODEWITHSUNDEEP
sqlunixhive
Veeru Badavath
Veeru Badavath

Reputation: 19

Hive query to find difference between two timestamp

I have two timestamp request and response. I need to find out difference between two these two timestamp in millisecond as below.

Request: 2020-03-20 10:00:00:010
Response: 2020-03-20 10:00:00:020
Diff: 10 millisecond

I tried but could not get my required answer. I tried as below but it is giving me 0 instead of 10.

select (unix_timestamp(2020-03-20 10:00:00:010) - unix_timestamp(2020-03-20 10:00:00:020))

Upvotes: 0

Views: 268

Answers (2)

shashank
shashank

Reputation: 50

Hive timestamp will be always something like:

2020-03-20 01:50:19.158

To get the difference between the two timestamps, you can try running below query:

select date_format("2020-03-20 10:00:00.020",'S') -date_format("2020-03-20 10:00:00.010",'S');

If the millisecond part is separated by ":" , then you can get the difference by running below query:

select cast(substr("2020-03-20 10:00:00:020",-3) as int) -  cast(substr("2020-03-20 10:00:00:010",-3) as int);

Upvotes: 1

user3327034
user3327034

Reputation: 405

Thats because unix_timestamp trims out the millisecond portion. You need some regex to parse it - something like:

select cast(regexp_replace('2020-03-20 10:00:00:020', 
                           '(\\d{4})-(\\d{2})-(\\d{2}) (\\d{2}):(\\d{2}):(\\d{2}):(\\d{3})',
                           '$1-$2-$3 $4:$5:$6.$7') as timestamp);

OR

SELECT ROUND((CAST(CAST('2020-03-20 10:00:00.020' AS TIMESTAMP) AS DOUBLE) 
            - CAST(CAST('2020-03-20 10:00:00.010' AS TIMESTAMP) AS DOUBLE)) * 1000)
as timediff

The millisecond portion should of the form yyyy-mm-dd hh:mm:ss.SSS So you may have to replace the ":" with "." for milliseconds.

Upvotes: 1

Related Questions