CODEWITHSUNDEEP
sqloracle-databasedate
pramod modankapu
pramod modankapu

Reputation: 1

Oracle time difference is greater than 5 mins

I have two date column in one table Say date1 and date2, I want to find the difference b/w these two column and result of the value, Want to compare with greater than 5 mins

Ex:

Select date1-date2 from XYZ   // Result of this something like +00 00:05:00.000000 
where date1 - date2 is greater than 5 mins

Above query is syntactically not correct, Written what I want to archive it

Can someone help me on this

Thanks Pramod

Upvotes: 0

Views: 2021

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

You said they are dates, but then

Result of this something like +00 00:05:00.000000

which is an interval; which means the columns are timestamps, not dates. So, you can compare using an interval as well:

select date1 - date2
from XYZ
where date1 - date2 > interval '5' minute

db<>fiddle demo

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142720

Difference of two DATE datatype values (and that's what you wrote) is number of days between them, so you'll have to "convert" it to minutes:

select (date1 - date2) * (24 * 60) number_of_minutes
from your_table

(24 * 60 because hour has 60 minutes, and day has 24 hours).

Therefore, you'd check

select case when (date1 - date2) * (24 * 60) > 5 then 'More than 5 minutes'
            else 'Some other message'
       end as result
from your_table

Upvotes: 0

Related Questions