rg*****@aol.com (Bob) wrote in message news:<ee**************************@posting.google. com>...
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?
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;
/