Re: how can I calculate the difference in time between two oracle date/times

From: Henry Hayden <gumzilla_at_voicenet.com>
Date: Sat, 12 Jan 2002 12:09:06 GMT
Message-ID: <CHV%7.52$Ld.8912_at_news2.voicenet.com>

Here is something from one of the various Oracle FAQs. It should be just what you're looking for. My apologies to the original author- the credit should go where it is rightfully due. I don't know which FAQ it's from, or I'd point that out, too.

To break the diff between two dates into days, hours, minutes, sec -- you can use the following:

select to_char( created, 'dd-mon-yyyy hh24:mi:ss' ),

       trunc( sysdate-created ) "Dy",
       trunc( mod( (sysdate-created)*24, 24 ) )  "Hr",
       trunc( mod( (sysdate-created)*24*60, 60 ) )  "Mi",

trunc( mod( to_char(sysdate,'SSSSS')-to_char(created,'SSSSS'), 60 ) ) "Sec",

       to_char( sysdate, 'dd-mon-yyyy hh24:mi:ss' ),
       sysdate-created "Tdy",
       (sysdate-created)*24 "Thr",
       (sysdate-created)*26*60 "Tmi",
       (sysdate-created)*26*60*60 "Tsec"

from all_users
where rownum < 50
/

Dy gives you number of days between two dates (partial days discarded). Tdy gives you total days including fractions (eg: you'll get 1.5 for 1 and 1/2 days). Likewise for HR and THR and so on.

Sybase users are used to using datediff in the database and are many times baffled by the lack of a function to do date arithemetic that they assume Oracle cannot do it. It is really just that date arithmetic is so trivial that a specialized function like datediff is not needed. Just subtract. You get the difference in days. Multiply by 24 -- hours, multiply by 60 minutes, multiply by 60 -- seconds.

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,

        p_d1   in date,
        p_d2   in date ) return number
     as
        l_result    number;
     begin
        select (p_d2-p_d1) * decode( upper(p_what),
               'SS',
                24*60*60,
                'MI',
                24*60,
                'HH',
                24,
                NULL )

into l_result from dual;
return l_result;
end;

Function created

Now, I just create a view to demonstrate with: SQL> create or replace view temp_view
  2 as
  3 select to_date('01-JAN-1999 12:02:04', 'dd-mon-yyyy hh24:mi:ss' ) d1,   4 to_date('15-MAR-1999 01:34:23', 'dd-mon-yyyy hh24:mi:ss' ) d2   5 from dual
  6 /
View created.

SQL> select datediff( 'ss', d1, d2 ) seconds from temp_view;

SECONDS

6269539

SQL> select datediff( 'mi', d1, d2 ) minutes from temp_view;

MINUTES

104492.317

SQL> select datediff( 'hh', d1, d2 ) hours from temp_view;

HOURS

1741.53861

Hope this helps.

Henry

"Brian Penn" <ecl6bp_at_leeds.ac.uk> wrote in message news:f58eff45.0201110315.782610bc_at_posting.google.com...
> Hello,