Re: Time function?

From: Chris Weiss <weisschr_at_pilot.msu.edu>
Date: Tue, 25 May 1999 08:50:06 -0400
Message-ID: <7ie6a8$n1d$1@msunews.cl.msu.edu>

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.---
Received on Tue May 25 1999 - 07:50:06 CDT