469,167 Members | 1,256 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

Timestamps and computing aggregate time.

Bob
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?
Jul 19 '05 #1
1 5885
Bob
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;
/
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by lkrubner | last post: by
4 posts views Thread by Craig G | last post: by
1 post views Thread by Najib Abi Fadel | last post: by
16 posts views Thread by maruk2 | last post: by
14 posts views Thread by Aaron Watters | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.