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

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 2

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 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

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).