Re: date/time problem

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 23 Oct 1998 14:22:41 GMT
Message-ID: <3633912c.6981348@192.86.155.100>

A copy of this was sent to andy sykes <andrew.sykes_at_holland.sun.com> (if that email address didn't require changing) On Fri, 23 Oct 1998 11:42:02 +0200, you wrote:

>Hi All,
>I want to compare two dates and display the difference in Hours,Mins &
>seconds. Has anyone had to do this or can anyone suggest a solution ?
>I have tried the months_between function but it doesn't appear to work
>when the dates are the same e.g
>
>SELECT MONTHS_BETWEEN(TO_DATE('10-23-1998 09:30:00', 'MM-DD-YYYY
>HH:MI:SS'),TO_DATE('10-23-1998 11:35:00', 'MM-DD-YYYY HH:MI:SS'))
>"Months" FROM DUAL
>
>returns ;
>
> Months
>----------
> 0
>Assuming that this was to give me a decimal value corresponding to the
>time,(the help says that is should), how would I convert that into
>meaningfull Hrs,Min & Secs values ?
>I would really appreciate some suggestions.
>Regards
>Andy

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 Fri Oct 23 1998 - 09:22:41 CDT