rgants1@aol.com (Bob) wrote in message news:<ee9df281.0406030855.15cd2121@posting.google. com>...[color=blue]
> thanks in advance,
>
>
> I am using Oracle 9i. I am looping through records via plsql and
> subtracting 2 timestamp datatypes.
>
> myTime := (disconTime - startTime);
>
> my problem is I dont understand how to get an aggregated variable out
> of myTime. What I want is a total amount of time used. I would like to
> be able to get it in minutes if possible.
>
> so for example what I get as output from the above is as follows:
>
> +000000000 00:01:01.400000
> +000000000 00:01:45.500000
> +000000000 00:00:19.700000
> +000000000 00:09:38.200000
> +000000000 00:01:18.800000
> +000000000 00:00:34.100000
> +000000000 00:00:43.500000
> +000000000 00:01:12.100000
> +000000000 00:01:04.500000
> +000000000 00:03:00.800000
> +000000000 00:02:12.200000
>
> how can I add these up for a grand total of time used?[/color]
I have the following solution figured out. In short what I am doing is
extracting elements of the timestamp and converting to a number and
changing to seconds. This should work fine now that I have a number.
The trouble was with trying to aggregate an interval.
I am migrating from a sybase environment, in sybase using a datediff
in milliseconds solved the problem. It does not seem like oracle has a
similar function.
CREATE OR REPLACE FUNCTION addem(startDateIn IN timestamp,disconDateIn
IN timestamp) RETURN NUMBER
IS
duration INTERVAL DAY TO SECOND;
myHour NUMBER;
myMinute NUMBER;
mySecond NUMBER;
totalSeconds NUMBER;
BEGIN
duration := (disconDateIn - startDateIn);
myHour := (to_number(extract(HOUR FROM duration))*3600);
myMinute := (to_number(extract(MINUTE FROM duration))*60);
mySecond := to_number(extract(SECOND FROM duration));
--dbms_output.put_line ('Hours as a number : '|| myHour);
--dbms_output.put_line ('Minutes as a number : '|| myMinute);
--dbms_output.put_line ('Secs as a number : '|| mySecond);
totalSeconds := (myHour + myMinute + mySecond);
dbms_output.put_line ('Total Seconds Used: '|| totalSeconds);
return(totalSeconds);
END;
/
