Re: TIME Arithmetic

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 20 Oct 1998 13:52:47 GMT
Message-ID: <3634959e.92558752@192.86.155.100>

A copy of this was sent to Vinnie <salernov_at_rl.af.mil> (if that email address didn't require changing) On Tue, 20 Oct 1998 09:27:58 -0400, you wrote:

>Can you subtract one time from another?
>
>EX.
>
>v_start := SYSDATE;
>v_end := v_stop_time;
>
>v_lapse := v_end - v_start
>
>Thanks
>Vinnie
>Litton/PRC

short answer = yes.

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.

To get the hours between 2 times, simply:

select ( date1 - date2 ) * 24 from T;

thats the number of hours (including the fractional component of an hour so you might get something like 3.1232253 meaning 3 hours and 1/12'th of an hour.

To break the diff between 2 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 2 dates (partial days discarded). Tdy gives you total days including fractions (eg: you'll get 1.5 for 1 and 1/2 days)

Hr/Thr = hours
Mi/Tmi = minutes
Sec/Tsec = seconds...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Oct 20 1998 - 08:52:47 CDT