Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Time function?

Re: Time function?

From: <grider22_at_my-dejanews.com>
Date: Wed, 26 May 1999 12:22:19 GMT
Message-ID: <7igp1q$kuq$1@nnrp1.deja.com>


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.--- Received on Wed May 26 1999 - 07:22:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US