Re: Time function?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 24 May 1999 20:40:50 GMT
Message-ID: <3749b943.30431217@newshost.us.oracle.com>

A copy of this was sent to grider22_at_my-dejanews.com (if that email address didn't require changing) On Mon, 24 May 1999 18:49:26 GMT, you wrote:

>Hi,
>
>Does anyone know of a function or how to create one that will take in
>two different time values and return the difference in hours, minutes
>and seconds as separate variables.
>
>Thanks
>Stephen
>
>
>--== Sent via Deja.com http://www.deja.com/ ==--
>---Share what you know. Learn what you don't.---

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...

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon May 24 1999 - 15:40:50 CDT