Collected posts: Oracle, SQL, PL/SQL, Performance, Security...(More than 300 blogs)

mercredi 19 novembre 2014

Getting the difference between Dates



To get the difference between  two dates;
The below function return the interval time between two dates:

create or replace function 
get_elapsed (pstart in date , pend in date )
 return interval day to second
as
begin
    return (cast(pend as timestamp)- cast(pstart as timestamp))  DAY TO SECOND;
end ;



SELECT GET_ELAPSED(
to_date('19/11/2014 12:10:20', 'DD/MM/YYYY HH24:MI:SS'), to_date('19/11/2014 13:10:20', 'DD/MM/YYYY HH24:MI:SS') 
)  ELAPSED
 FROM DUAL;

ELAPSED:
+000000000 01:00:00

We can extract hour :

SELECT EXTRACT(hour from GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'))) HOUR
 
FROM DUAL;

HOUR:
1

We can extract hours,minutes and seconds 



SELECT
   GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:00:00',
                                   'DD/MM/YYYY HH24:MI:SS') ) ELAPSED,
 
   EXTRACT(HOUR
FROM GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:00:00',
                                   'DD/MM/YYYY HH24:MI:SS')) ) HOUR,
             EXTRACT(MINUTE
FROM GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:00:00',
                                   'DD/MM/YYYY HH24:MI:SS')) ) MINUTE,      
           EXTRACT(
SECOND FROM GET_ELAPSED(to_date('19/11/2014 12:10:20',
                                   'DD/MM/YYYY HH24:MI:SS'),
                           to_date('19/11/2014 13:00:00',
                                   'DD/MM/YYYY HH24:MI:SS')) )
SECOND                                          
 
FROM DUAL;



    ELAPSED                 HOUR MINUTE SECOND
+000000000 00:49:40 0 49 40


You can refer to Database SQL Language Reference to get more details about Interval type  and extract function;