CODEWITHSUNDEEP
sqlhivetimestamphiveql
Sarah
Sarah

Reputation: 161

Time difference between two date fields

I have a date field names "dts" in string format. I want to find out all the records based on their time differences (in hours). The run event time should be greater than or equal to eat event.

enter image description here

The output should be:

enter image description here

Upvotes: 1

Views: 733

Answers (1)

leftjoin
leftjoin

Reputation: 38290

Convert timestamps to seconds, then subtract, divide result by 3600 to get hours, use case+count to count by ranges, something like this:

select count(case when diff_hrs >24 then 1 end) as more_24,
       count(case when diff_hrs <=24 then 1 end) as less_than_24,
       ...
       count(case when diff_hrs >=2 and diff_hrs <=3 then 1 end) as hrs_2_to_3,
       ...
from
(
select
abs(unix_timestamp(dts) - unix_timestamp(dts-eat)))/60/60 as diff_hrs
from table
)s;

Upvotes: 1

Related Questions