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

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 2

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;