Oracle Date Subtraction

How would i write an expression that gives me the difference between two dates in days, hours, minutes, seconds, etc? By default subtracting two dates in oracle returns days as a

Solution 1:

Use:

SELECT TO_CHAR(date1,'MMDDYYYY:HH24:MI:SS') date1,
       TO_CHAR(date2,'MMDDYYYY:HH24:MI:SS') date2,
       TRUNC(86400*(date2-date1)) -60*(TRUNC((86400*(date2-date1))/60)) seconds,
       TRUNC((86400*(date2-date1))/60) -60*(TRUNC(((86400*(date2-date1))/60)/60)) minutes,
       TRUNC(((86400*(date2-date1))/60)/60) -24*(TRUNC((((86400*(date2-date1))/60)/60)/24)) hours,
       TRUNC((((86400*(date2-date1))/60)/60)/24) days,
       TRUNC(((((86400*(date2-date1))/60)/60)/24)/7) weeks
  FROMTABLE

Reference: A Comparison of Oracle's DATE and TIMESTAMP Datatypes

Solution 2:

That decimal is the number of days difference between the two dates provided. You can do a little math to convert that to days, hours, minutes, seconds, etc.

EDIT: I see what you're looking for. I'm sure there's an easier way, but I would probably accomplish it thusly:

select trunc(5.3574585) days, 
       trunc(mod((5.3574585) * 24, 24)) hours, 
       trunc(mod((5.3574585) * 24 * 60, 60)) minutes, 
       trunc(mod((5.3574585) * 24 * 60 * 60, 60)) seconds 
  from dual;

...where 5.3574585 is the number of days returned by the subtraction...

Note: this isn't really tested, it's off the top of my head.

Solution 3:

Why not just convert to timestamp and implicitly use an interval day to second data type?

select to_timestamp(sysdate+1.1234) - to_timestamp(sysdate) diff
from dual
/

DIFF        
----------- 12:57:42.0

Solution 4:

You could convert the dates to timestamps and use native functionality to get the individual components out...

SELECTEXTRACT( DAYFROM ( end_timestamp - start_timestamp ) )   days
     , EXTRACT( HOURFROM ( end_timestamp - start_timestamp ) )   hours
     , EXTRACT( MINUTEFROM ( end_timestamp - start_timestamp ) )   minutes
     , EXTRACT( SECONDFROM ( end_timestamp - start_timestamp ) )   seconds
  FROM ( SELECT TO_TIMESTAMP( TO_CHAR( start_date, 'DD/MM/YYYY HH24:MI:SS' )
                            , 'DD/MM/YYYY HH24:MI:SS' )   start_timestamp
              , TO_TIMESTAMP( TO_CHAR( end_date, 'DD/MM/YYYY HH24:MI:SS' )
                            , 'DD/MM/YYYY HH24:MI:SS' )   end_timestamp
           FROM ( SELECT TO_DATE( '01/10/2009 14:25:01'
                                , 'DD/MM/YYYY HH24:MI:SS' )   start_date
                       , TO_DATE( '03/10/2009 23:09:15'
                                , 'DD/MM/YYYY HH24:MI:SS' )   end_date
                    FROM dual
                )
       )