Re: Time function?

grider22_at_my-dejanews.com wrote:
>
> Thanks guys for your replies. Here is the final function that I came up
> with. I think I used a little from everyone's suggestions. :)
>
> A.STOP_TIME := (A.STOP_TIME || '00');
> A.START_TIME := (A.START_TIME || '00');
> DAYS := (A.STOP_DATE) - (A.START_DATE);
>
> IF DAYS >= 1 THEN
>
> date_diff := 3600*TO_NUMBER(SUBSTR( A.STOP_TIME,1,2)) +
> 60*TO_NUMBER(SUBSTR( A.STOP_TIME,3,2))
> + TO_NUMBER(SUBSTR( A.STOP_TIME,5,2)) + (DAYS*86400);
>
> ELSE
>
> date_diff := 3600*TO_NUMBER(SUBSTR( A.STOP_TIME,1,2)) +
> 60*TO_NUMBER(SUBSTR( A.STOP_TIME,3,2))
> + TO_NUMBER(SUBSTR( A.STOP_TIME,5,2));
>
> END IF;
>
> date_diff := date_diff - 3600*TO_NUMBER(SUBSTR(A.START_TIME,1,2)) -
> 60*TO_NUMBER(SUBSTR(A.START_TIME,3,2)) -
> TO_NUMBER(SUBSTR(A.START_TIME,5,2));
>
> hours := trunc(date_diff/3600);
> minutes := trunc(date_diff/60 - 60*hours);
> seconds := trunc(date_diff - 60*minutes - 3600*hours);
>
> In article <7ie6a8$n1d$1_at_msunews.cl.msu.edu>,
> "Chris Weiss" <weisschr_at_pilot.msu.edu> wrote:
> > I was doing some playing around and ran into round off errors in doing
> date
> > arithmetic. The following procedure takes times as strings and
> produces the
> > difference the way you requested without rounding errors:
> >
> > create or replace procedure time_diff(time_val1 IN VARCHAR2,
> > time_val2 IN VARCHAR2,
> > hour_diff OUT NUMBER,
> > MIN_diff OUT NUMBER,
> > SEC_diff OUT NUMBER) IS
> >
> > date_diff NUMBER;
> > hour_val NUMBER;
> > min_val NUMBER;
> > sec_val NUMBER;
> >
> > BEGIN
> >
> > IF length(NVL(time_val1,'1')) != 8 OR length(NVL(time_val2,'1')) != 8
> THEN
> > RAISE_APPLICATION_ERROR (-20001,'Bad time values '||time_val1||' -
> > '||time_val2);
> > END IF;
> >
> > date_diff := 3600*TO_NUMBER(SUBSTR(TIME_VAL1,1,2)) +
> > 60*TO_NUMBER(SUBSTR(TIME_VAL1,4,2)) +
> > TO_NUMBER(SUBSTR(TIME_VAL1,7,2));
> > date_diff := date_diff - 3600*TO_NUMBER(SUBSTR(TIME_VAL2,1,2)) -
> > 60*TO_NUMBER(SUBSTR(TIME_VAL2,4,2)) -
> > TO_NUMBER(SUBSTR(TIME_VAL2,7,2));
> >
> > hour_diff := trunc(date_diff/3600);
> >
> > IF hour_diff >24 or hour_diff< -24 THEN
> > RAISE_APPLICATION_ERROR (-20001,'Bad time values '||time_val1||' -
> > '||time_val2);
> > END IF;
> >
> > min_diff := trunc(date_diff/60 - 60*hour_diff);
> >
> > IF min_diff >60 or min_diff< -60 THEN
> > RAISE_APPLICATION_ERROR (-20001,'Bad time values '||time_val1||' -
> > '||time_val2);
> > END IF;
> >
> > sec_diff := trunc(date_diff - 60*min_diff - 3600*hour_diff);
> >
> > IF sec_diff >60 or sec_diff< -60 THEN
> > RAISE_APPLICATION_ERROR (-20001,'Bad time values '||time_val1||' -
> > '||time_val2);
> > END IF;
> >
> > EXCEPTION
> > WHEN VALUE_ERROR or invalid_number THEN
> > RAISE_APPLICATION_ERROR (-20001,'Bad time values
> '||time_val1||' -
> > '||time_val2);
> >
> > END;
> >
> > You can play around with the following anonymous block to see how the
> > procedure works.
> >
> > declare
> >
> > t1 varchar2(8);
> > t2 varchar2(8);
> > hd number;
> > md number;
> > sd number;
> >
> > begin
> >
> > t1 := '14:23:37';
> > t2 := '12:24:39';
> >
> > time_diff(t1,t2,hd,md,sd);
> >
> > dbms_output.put_line(to_char(hd)||':'||to_char(md)||':'||to_char(sd));
> >
> > end;
> >
> > I wrote this on an 4.0 NT server using Oracle Enterprise Server 8.0.5.
> >
> > Christopher Weiss
> > Professional Services Division
> > Compuware Corporation
> >
> > <grider22_at_my-dejanews.com> wrote in message
> > news:7ic6vl$cu1$1_at_nnrp1.deja.com...
> > > 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.---
> >
> >
>
> --== Sent via Deja.com http://www.deja.com/ ==--
> ---Share what you know. Learn what you don't.---
I think this single SQL Statement does it all

select trunc((to_date(t1, 'hh24:mi:ss') - to_date(t1, 'hh24:mi:ss')) * 24) Hrs,

       trunc(mod((to_date(t1, 'hh24:mi:ss') - to_date(t1, 'hh24:mi:ss')) * 24 * 60, 60)) Mins,

       trunc(mod(to_date(t1, 'hh24:mi:ss') - to_date(t1, 'hh24:mi:ss')) * 24 * 60 * 60, 60)) Sec
from dual;

A bit long but, i guess it works.

Thanks
Prasad Received on Thu Jun 03 1999 - 13:59:49 CDT