AVG and SUM for INTERVALs

Presumably due to an oversight, Oracle have not yet overloaded the SUM and AVG functions to support the INTERVAL DAY TO SECOND datatype. This is a pity, as it greatly reduces the usefulness of these types for database columns - it's not much use having an "elapsed time" column you can't add up, so many people resort to a plain numeric column representing times in seconds.

Fortunately it's not hard to write your own. Here are my AVG_DSINTERVAL and SUM_DSINTERVAL aggregate/analytic functions.

-- AVG and SUM aggregate functions for INTERVAL DAY TO SECOND values.
-- William Robertson 2004, www.williamrobertson.net
-- Updated 11/2012 to use DSINTERVAL_UNCONSTRAINED type for interval parameters.
--
-- Since introducing the interval types in 9i, Oracle has neglected to overload the
-- aggregate functions SUM, AVG etc for them:
--
-- SQL> SELECT SUM(INTERVAL '1' SECOND) FROM dual
-- SELECT SUM(INTERVAL '1' SECOND) FROM dual
--            *
-- ERROR at line 1:
-- ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND
--
-- Until they get around to it, you can create your own:
--
-- SQL> SELECT SUM_DSINTERVAL(INTERVAL '1' SECOND) FROM dual;
--
-- SUM_DSINTERVAL(INTERVAL'1'SECOND)
-- ---------------------------------------------------------------------------
-- +000000000 00:00:01.000000000
--
-- 1 row selected.

drop type dsinterval_avg;
drop type dsinterval_sum;

create or replace type dsinterval_avg as object
( total interval day(6) to second
, items integer
, static function ODCIAggregateInitialize
   ( sctx in out dsinterval_avg )
   return number
, member function ODCIAggregateIterate
   ( self in out dsinterval_avg
   , value in interval day to second )
   return number
, member function ODCIAggregateTerminate
   ( self in dsinterval_avg
   , returnvalue out dsinterval_unconstrained
   , flags in number )
   return number
, member function ODCIAggregateMerge
   ( self in out dsinterval_avg
   , ctx2 in dsinterval_avg )
   return number
);
/

show errors

create or replace type body dsinterval_avg
as
   static function ODCIAggregateInitialize
      ( sctx in out dsinterval_avg )
      return number
   is
   begin
      sctx := new dsinterval_avg(interval '0' second, 0);
      return ODCIConst.Success;
   end;

   member function ODCIAggregateIterate
      ( self in out dsinterval_avg
      , value in interval day to second )
      return number
   is
   begin
      self.total := self.total + value;
      self.items := self.items + 1;
      return ODCIConst.Success;
   end;

   member function ODCIAggregateTerminate
      ( self in dsinterval_avg
      , returnValue out dsinterval_unconstrained
      , flags in number )
      return number
   is
   begin
      returnvalue := self.total / self.items;
      return ODCIConst.Success;
   end;

   member function ODCIAggregateMerge
      ( self in out dsinterval_avg
      , ctx2 in dsinterval_avg )
      return number
   is
   begin
      -- The ODCIAggregateMerge function is used to combine results of
      -- parallel queries. Presumably it is applied prior to DCIAggregateTerminate,
      -- in which case it should probably combine totals from each thread.
      -- Needs testing.

      self.total := self.total + ctx2.total;
      self.items := self.items + ctx2.items;

      return ODCIConst.Success;
   end;
end;
/

show errors

-- Corresponding SUM aggregate:

create or replace type dsinterval_sum as object
( total interval day(6) to second
, static function ODCIAggregateInitialize
   ( sctx in out dsinterval_sum )
   return number
, member function ODCIAggregateIterate
   ( self in out dsinterval_sum
   , value in interval day to second )
   return number
, member function ODCIAggregateTerminate
   ( self in dsinterval_sum
   , returnValue out dsinterval_unconstrained
   , flags in number )
   return number
, member function ODCIAggregateMerge
   ( self in out dsinterval_sum
   , ctx2 in dsinterval_sum )
   return number
);
/

show errors

create or replace type body dsinterval_sum
as
   static function ODCIAggregateInitialize
      ( sctx in out dsinterval_sum )
      return number
   is
   begin
      sctx := new dsinterval_sum(interval '0' second);
      return ODCIConst.Success;
   end;

   member function ODCIAggregateIterate
      ( self in out dsinterval_sum
      , value in interval day to second )
      return number
   is
   begin
      self.total := self.total + value;
      return ODCIConst.Success;
   end;

   member function ODCIAggregateTerminate
      ( self in dsinterval_sum
      , returnValue out dsinterval_unconstrained
      , flags in number )
      return number
   is
   begin
      returnvalue := self.total;
      return ODCIConst.Success;
   end;

   member function ODCIAggregateMerge
      ( self in out dsinterval_sum
      , ctx2 in dsinterval_sum )
      return number
   is
   begin
      -- The ODCIAggregateMerge function is used to combine results of
      -- parallel queries. Presumably it is applied prior to DCIAggregateTerminate,
      -- in which case it should probably combine totals from each thread.
      -- Needs testing.

      self.total := self.total + ctx2.total;

      return ODCIConst.Success;
   end;
end;
/

show errors

create or replace function sum_dsinterval
   ( input interval day to second )
   return interval day to second
   parallel_enable
   aggregate using dsinterval_sum;
/

with tstamp as
   ( select timestamp '2012-11-12 12:00:00' as t1
          , timestamp '2012-11-13 13:01:02' as t2
     from dual )
select sum_dsinterval(i) from ( select t2 - t1 as i from tstamp );
create or replace function avg_dsinterval
   ( input interval day to second )
   return interval day to second
   parallel_enable
   aggregate using dsinterval_avg;
/

with tstamp as
   ( select timestamp '2012-11-12 12:00:00' as t1
          , timestamp '2012-11-13 13:01:02' as t2
     from dual
     union all
     select timestamp '2012-11-12 12:34:56' as t1
          , timestamp '2012-11-13 14:56:08' as t2
     from dual )
select avg_dsinterval(i) from ( select t2 - t1 as i from tstamp );
/

with tstamp as
    ( select timestamp '2012-01-12 12:00:00' as t1
           , timestamp '2012-11-13 13:01:02' as t2
      from dual
      union all
      select timestamp '2012-11-12 12:34:56' as t1
           , timestamp '2012-11-13 14:56:08' as t2
      from dual )
select avg_dsinterval(i) from ( select t2 - t1 as i from tstamp )
/