How to average time intervals?

Question

In Oracle 10g I have a table that holds timestamps showing how long certain operations took. It has two timestamp fields: starttime and endtime. I want to find averages of the durations given by these timestamps. I try:

select avg(endtime-starttime) from timings;

But get:

SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND

This works:

select
     avg(extract( second from  endtime - starttime) +
        extract ( minute from  endtime - starttime) * 60 +
        extract ( hour   from  endtime - starttime) * 3600) from timings;

But is really slow.

Any better way to turn intervals into numbers of seconds, or some other way do this?

EDIT: What was really slowing this down was the fact that I had some endtime's before the starttime's. For some reason that made this calculation incredibly slow. My underlying problem was solved by eliminating them from the query set. I also just defined a function to do this conversion easier:

FUNCTION fn_interval_to_sec ( i IN INTERVAL DAY TO SECOND )
RETURN NUMBER
IS
  numSecs NUMBER;
BEGIN
  numSecs := ((extract(day from i) * 24
         + extract(hour from i) )*60
         + extract(minute from i) )*60
         + extract(second from i);
  RETURN numSecs;
END;

Answer 1

The cleanest way is to write your own aggregate function to do this, since it will handle this the most cleanly (handles sub-second resolution, etc.).

In fact, this question was asked (and answered) on asktom.oracle.com a while back (article includes source code).

Answer 2

There is a shorter, faster and nicer way to get DATETIME difference in seconds in Oracle than that hairy formula with multiple extracts.

Just try this to get response time in seconds:

(sysdate + (endtime - starttime)*24*60*60 - sysdate)

It also preserves fractional part of seconds when subtracting TIMESTAMPs.

See http://kennethxu.blogspot.com/2009/04/converting-oracle-interval-data-type-to.html for some details.

---

Note that custom pl/sql functions have significant performace overhead that may be not suitable for heavy queries.

Answer 3

If your endtime and starttime aren't within a second of eachother, you can cast your timestamps as dates and do date arithmetic:

select avg(cast(endtime as date)-cast(starttime as date))*24*60*60 
  from timings;

Answer 4

Well, this is a really quick and dirty method, but what about storing the seconds difference in a separate column (you'll need to use a trigger or manually update this if the record changes) and averaging over that column?

Answer 5

It doesn't look like there is any function to do an explicit conversion of INTERVAL DAY TO SECOND to NUMBER in Oracle. See the table at the end of this document which implies there is no such conversion.

Other sources seem to indicate that the method you're using is the only way to get a number from the INTERVAL DAY TO SECOND datatype.

The only other thing you could try in this particular case would be to convert to number before subtracting them, but since that'll do twice as many extractions, it will likely be even slower:

select
     avg(
       (extract( second from endtime)  +
        extract ( minute from endtime) * 60 +
        extract ( hour   from  endtime ) * 3600) - 
       (extract( second from starttime)  +
        extract ( minute from starttime) * 60 +
        extract ( hour   from  starttime ) * 3600)
      ) from timings;

Answer 6

SQL Fiddle

Oracle 11g R2 Schema Setup:

Create a type to use when performing a custom aggregation:

CREATE TYPE IntervalAverageType AS OBJECT(
  total INTERVAL DAY(9) TO SECOND(9),
  ct    INTEGER,

  STATIC FUNCTION ODCIAggregateInitialize(
    ctx         IN OUT IntervalAverageType
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(
    self        IN OUT IntervalAverageType,
    value       IN     INTERVAL DAY TO SECOND
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT IntervalAverageType,
    returnValue    OUT INTERVAL DAY TO SECOND,
    flags       IN     NUMBER
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT IntervalAverageType,
    ctx         IN OUT IntervalAverageType
  ) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY IntervalAverageType
IS
  STATIC FUNCTION ODCIAggregateInitialize(
    ctx         IN OUT IntervalAverageType
  ) RETURN NUMBER
  IS
  BEGIN
    ctx := IntervalAverageType( INTERVAL '0' DAY, 0 );
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(
    self        IN OUT IntervalAverageType,
    value       IN     INTERVAL DAY TO SECOND
  ) RETURN NUMBER
  IS
  BEGIN
    IF value IS NOT NULL THEN
      self.total := self.total + value;
      self.ct    := self.ct + 1;
    END IF;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT IntervalAverageType,
    returnValue    OUT INTERVAL DAY TO SECOND,
    flags       IN     NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    IF self.ct = 0 THEN
      returnValue := NULL;
    ELSE
      returnValue := self.total / self.ct;
    END IF;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT IntervalAverageType,
    ctx         IN OUT IntervalAverageType
  ) RETURN NUMBER
  IS
  BEGIN
    self.total := self.total + ctx.total;
    self.ct    := self.ct + ctx.ct;
    RETURN ODCIConst.SUCCESS;
  END;
END;
/

Then you can create a custom aggregation function:

CREATE FUNCTION AVERAGE( difference INTERVAL DAY TO SECOND )
RETURN INTERVAL DAY TO SECOND
PARALLEL_ENABLE AGGREGATE USING IntervalAverageType;
/

Query 1:

WITH INTERVALS( diff ) AS (
  SELECT INTERVAL '0' DAY FROM DUAL UNION ALL
  SELECT INTERVAL '1' DAY FROM DUAL UNION ALL
  SELECT INTERVAL '-1' DAY FROM DUAL UNION ALL
  SELECT INTERVAL '8' HOUR FROM DUAL UNION ALL
  SELECT NULL FROM DUAL
)
SELECT AVERAGE( diff ) FROM intervals

Results:

| AVERAGE(DIFF) |
|---------------|
|     0 2:0:0.0 |