In Oracle, is there a function that calculates the difference between two Dates?

Question

In Oracle, is there a function that calculates the difference between two Dates? If not, is a way to display the difference between two dates in hours and minutes? Check query below.

SELECT Round(max((EndDate - StartDate ) * 24), 2) as MaximumScheduleTime, Round(min((EndDate - StartDate) * 24), 2) as MinimumScheduleTime, Round(avg((EndDate - StartDate) * 24), 2) as AveragegScheduleTime FROM table1

Answer 1

With Oracle Dates, this is pretty trivial, you can get either TOTAL (days, hours, minutes, seconds) between 2 dates simply by subtracting them or with a little mod'ing you can get Days/Hours/Minutes/Seconds between.

http://asktom.oracle.com/tkyte/Misc/DateDiff.html

Also, from the above link:

If you really want 'datediff' in your database, you can just do something like this:

SQL> create or replace function datediff( p_what in varchar2, 
  2                                       p_d1   in date, 
  3                                       p_d2   in date ) return number 
  4  as 
  5      l_result    number; 
  6  begin 
  7      select (p_d2-p_d1) * 
  8             decode( upper(p_what), 
  9                     'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL ) 
 10       into l_result from dual; 
 11 
 11      return l_result; 
 12  end; 
 13  /
Function created

Answer 2

You can use these functions :

1) EXTRACT(element FROM temporal_value)

2) NUMTOYMINTERVAL (n, unit)

3) NUMTODSINTERVAL (n, unit).

For example :

SELECT EXTRACT(DAY FROM NUMTODSINTERVAL(end_time - start_time, 'DAY'))
   || ' days ' || 
   EXTRACT(HOUR FROM NUMTODSINTERVAL(end_time - start_time, 'DAY'))
   ||':'|| 
   EXTRACT(MINUTE FROM NUMTODSINTERVAL(end_time - start_time, 'DAY')) 
   ||':'||
   EXTRACT(SECOND FROM NUMTODSINTERVAL(end_time - start_time, 'DAY')) 
   "Lead Time"
FROM table;

Answer 3

You can subtract two dates in Oracle. The result is a FLOAT which represents the number of days between the two dates. You can do simple arithmetic on the fractional part to calculate the hours, minutes and seconds.

Here's an example:

SELECT TO_DATE('2000/01/02:12:00:00PM', 'yyyy/mm/dd:hh:mi:ssam')-TO_DATE('2000/01/01:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam') DAYS FROM DUAL

Results in: 1.5

Answer 4

Q: In Oracle, is there a function that calculates the difference between two Dates?

Just subtract one date expression from another to get the difference expressed as a number of days. The integer portion is the number of whole days, the fractional portion is the fraction of a day. Simple arithmetic after that, multiply by 24 to get hours.

Q: If not, is a way to display the difference between two dates in hours and minutes?

It's just a matter of expressing the duration as whole hours and remainder minutes.

We can go "old school" to get durations in hhhh:mi format using a combination of simple builtin functions:

SELECT decode(sign(t.maxst),-1,'-','')||to_char(floor(abs(t.maxst)/60))||
        decode(t.maxst,null,'',':')||to_char(mod(abs(t.maxst),60),'FM00')
         as MaximumScheduleTime
     , decode(sign(t.minst),-1,'-','')||to_char(floor(abs(t.minst)/60))||
        decode(t.minst,null,'',':')||to_char(mod(abs(t.minst),60),'FM00')
         as MinimumScheduleTime
     , decode(sign(t.avgst),-1,'-','')||to_char(floor(abs(t.avgst)/60))
        decode(t.avgst,null,'',':')||to_char(mod(abs(t.avgst),60),'FM00')
         as AverageScheduleTime
  FROM (
         SELECT round(max((EndDate - StartDate) *1440),0) as maxst
              , round(min((EndDate - StartDate) *1440),0) as minst
              , round(avg((EndDate - StartDate) *1440),0) as avgst
           FROM table1 
       ) t

---

Yeah, it's fugly, but it's pretty fast. Here's a simpler case, that shows better what's going on:

select dur                              as "minutes"
     , abs(dur)                         as "unsigned_minutes"
     , floor(abs(dur)/60)               as "unsigned_whole_hours"
     , to_char(floor(abs(dur)/60))      as "hhhh"
     , mod(abs(dur),60)                 as "unsigned_remainder_minutes"
     , to_char(mod(abs(dur),60),'FM00') as "mi"
     , decode(sign(dur),-1,'-','')      as "leading_sign"
     , decode(dur,null,'',':')          as "colon_separator"
  from (select round(( date_expr1 - date_expr2 )*24*60,0) as dur
          from ... 
       )

(replace date_expr1 and date_expr2 with date expressions)

let's unpack this

• date_expr1 - date_expr2 returns difference in number of days
• multiply by 1440 (24*60) to get duration in minutes
• round (or floor) to resolve fractional minutes into integer minutes
• divide by 60, integer quotient is hours, remainder is minutes
• abs function to get absolute value (change negative values to positive)
• to_char format model FM00 give two digits (leading zeros)
• use decode function to format a negative sign and a colon (if needed)

---

The SQL statement could be made less ugly using a PL/SQL function, one that takes two DATE arguments a duration in (fractional) days and returns formatted hhhh:mi

(untested)

create function hhhhmi(an_dur in number)
return varchar2 deterministic
is
begin
  if an_dur is null then
     return null;
  end if;
  return decode(sign(an_dur),-1,'-','')
    || to_char(floor(abs(an_dur)*24))
    ||':'||to_char(mod((abs(an_dur)*1440),60),'FM00');
end;

With the function defined:

SELECT hhhhmi(max(EndDate - StartDate)) as MaximumScheduleTime
     , hhhhmi(min(EndDate - StartDate)) as MinimumScheduleTime
     , hhhhmi(avg(EndDate - StartDate)) as AverageScheduleTime
  FROM table1