You are html tracking Visitor

Friday, August 28, 2009

Query to get the Number of Seconds or Minutes or Hours between 2 given days

Query to get the Number of Seconds or Minutes or Hours between 2 given days:-
----------------------------------------------------------------------------------------

Note:- I have prepared this query since I could not find any inbuilt functions or procedures in Oracle to get this. I had the requirement to treat 2 dates are same if differences is not more then 2 seconds.

For the Seconds:-
-------------------

SELECT ('Your first Date'-'Your another Date') * DECODE( UPPER('SS')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

Example:-

SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER('SS')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

For Minutes:-
---------------

SELECT ('Your first Date'-'Your another Date') * DECODE( UPPER('MI')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

Example:-

SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER('MI')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

For the Hours:-
-----------------

SELECT ('Your first Date'-'Your another Date') * DECODE( UPPER('HH')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

Example:-

SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER('HH')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

This is the simple decode function made for all the above 3 requirements. I hope this will help you.

3 comments:

Unknown said...
This comment has been removed by the author.
Unknown said...

Why not use this query:

Select numtodsinterval([first_date] - [second_date], 'day') from dual;

This will return an interval of type day to second that can be compared. For your particular requirement if you could then check to see if the calculated interval was within your range:

Select case when numtointerval([first_date] - [second_date], 'day') between numtodsinterval(1,'second') and numtodsinterval(-1,'second') then 'Same' else 'Different' end compare_dates from dual;

In this second query the first calculation is compared to two additional intervals of plus and minus 1 second. If it falls in that range it's considered the same otherwise it's different.

phani said...

Thanks for sharing. I am not aware of this function. Next time, I will try this and see if it works.

Thanks and regards,
Phani