Re: Timestamps and computing aggregate time.

rgants1_at_aol.com (Bob) wrote in message news:<ee9df281.0406030855.15cd2121_at_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);