title: How to average time intervals?
body: <p>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:</p>
  
  <pre><code>select avg(endtime-starttime) from timings;
  </code></pre>
  
  <p>But get: </p>
  
  <blockquote>
    <p>SQL Error: ORA-00932: inconsistent
    datatypes: expected NUMBER got
    INTERVAL DAY TO SECOND</p>
  </blockquote>
  
  <p>This works:</p>
  
  <pre><code>select
       avg(extract( second from  endtime - starttime) +
          extract ( minute from  endtime - starttime) * 60 +
          extract ( hour   from  endtime - starttime) * 3600) from timings;
  </code></pre>
  
  <p>But is really slow.</p>
  
  <p>Any better way to turn intervals into numbers of seconds, or some other way do this?</p>
  
  <p>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:</p>
  
  <pre><code>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;
  </code></pre>