Timestamps and computing aggregate time.

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Bob
    #1

    Timestamps and computing aggregate time.

    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?
    Tags: None
    • Bob
      #2
      Re: Timestamps and computing aggregate time.

      rgants1@aol.com (Bob) wrote in message news:<ee9df281. 0406030855.15cd 2121@posting.go ogle.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(startDate In IN timestamp,disco nDateIn
      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(extr act(HOUR FROM duration))*3600 );
      myMinute := (to_number(extr act(MINUTE FROM duration))*60);
      mySecond := to_number(extra ct(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(totalSec onds);
      END;
      /

        Comment

        Previous template Next
        Working...