Summing Interval Data Types

Something I’ve occasionally thought would be really nice is if Oracle had a good way of summing interval data types, much the same way that you can SUM() number data types.  Occasionally I’ve designed processes that record start and stop timestamps of operations.  If you want to know how long the operation lasted, you could do the end timestamp minus the start timestamp to get a DAY TO SECOND INTERVAL data type.

select end_timestamp - start_timestamp as job_duration
from job_runtimes;

If I want to know the total run time of a set of operations, there’s not a nice, slick, easy way to do this.

Usually, I get away with some quick-and-dirty code like this:

select numtodsinterval(
          sum(
               cast(end_timestamp as date) - cast(start_timestamp as date)
          )
       , 'DAY') duration
from job_runtimes;

• First, you must cast the timestamps as dates (meaning you lose any fractional seconds).
• Then you subtract the start date from the end date.  This gives you a number, which represents the number of days (possibly fractional).
• Oracle then allows you to sum up those numbers.
• If you want the number back in interval format, you can supply it to a NUMTODSINTERVAL() function, passing in ‘DAY’ as the second argument.

It’s not smooth. It loses precision. The maintaining developer has to double-take to try to figure out what it is that I’m doing.  Wouldn’t it be nice if there was some smooth way to just add up intervals?

Well, after giving it some thought, I decided to create my own.

Oracle defines how to roll your own aggregate Pl/SQL functions in the documentation using the data cartridge developer’s guide. Specifically, it’s under the user-defined aggregate functions interface.

In fact, at one place I’ve worked we used a home-rolled Pl/SQL function on Oracle database prior to 11.2 to do “comma-fied lists.” You see, in Oracle 11.2, LISTAGG() was introduced. It offered a lot more flexibility and our home-grown comma-fied lists function became antiquated.

Code to create SUM_DS_INTERVAL function

Below is the code I used to create my own SUM_DS_INTERVAL() function.  Notice that I’m calling it “DS” interval to show that this is for a DAY TO SECOND interval. You could just as easily create one for summing YEAR TO MONTH interval data types. DAY TO SECOND interval data types seems the most practical for the largest number of use-cases.

This code comes with ABSOLUTELY ZERO guarantees.  Use at your own risk. Before you try to use this in your production environment, I would strongly encourage you to test it.

create or replace type sum_interval_ds_typ
  authid definer
as object
(
    -- Get the biggest interval possible, so we reduce our risk of hitting "ORA-01873: the leading precision of the interval is too small"
    v_aggregated_interval interval day(9) to second(9)
  , static function ODCIAggregateInitialize(p_suminterval in out nocopy sum_interval_ds_typ) return number
  , member function ODCIAggregateIterate(self in out nocopy sum_interval_ds_typ, p_value in v_aggregated_interval%type) return number
  , member function ODCIAggregateMerge(self in out nocopy sum_interval_ds_typ, p_other in sum_interval_ds_typ) return number
  , member function ODCIAggregateTerminate(self in sum_interval_ds_typ, p_return_value out nocopy v_aggregated_interval%type, flags in string) return number
);
/
 
create or replace type body sum_interval_ds_typ
is
  static function ODCIAggregateInitialize(p_suminterval in out nocopy sum_interval_ds_typ)
    return number
  is
  begin
    p_suminterval := sum_interval_ds_typ(null);
    return ODCIConst.success;
  end ODCIAggregateInitialize;
 
  member function ODCIAggregateIterate(self in out nocopy sum_interval_ds_typ, p_value in v_aggregated_interval%type)
    return number
  is
  begin
    if self.v_aggregated_interval is null then
      self.v_aggregated_interval := p_value;
    else
      self.v_aggregated_interval := self.v_aggregated_interval + p_value;
    end if;
   
    return ODCIConst.success;
  end ODCIAggregateIterate;
 
  member function ODCIAggregateMerge(self in out nocopy sum_interval_ds_typ, p_other in sum_interval_ds_typ)
    return number
  is
  begin
    if self.v_aggregated_interval is null then
      self.v_aggregated_interval := p_other.v_aggregated_interval;
    elsif p_other.v_aggregated_interval is not null then
      self.v_aggregated_interval := self.v_aggregated_interval + p_other.v_aggregated_interval;
    end if;
   
    return ODCIConst.success;
  end ODCIAggregateMerge;
 
  member function ODCIAggregateTerminate(self in sum_interval_ds_typ, p_return_value out nocopy v_aggregated_interval%type, flags in string)
    return number
  is
  begin
    p_return_value := self.v_aggregated_interval;
   
    return ODCIConst.success;
  end ODCIAggregateTerminate;
 
end;
/
 
-- This is the function that we'll call
create or replace function sum_ds_interval(p_interval in interval day to second)
  return interval day to second parallel_enable
  authid definer
aggregate using sum_interval_ds_typ;
/
 
-- Give everyone on the database access to this function
grant execute on sum_ds_interval to public;
 
-- Create a public synonym for the function
create public synonym sum_ds_interval for sum_ds_interval;

Testing the ability to sum interval data types

Now that we have a SUM_DS_INTERVAL() function, let’s test it.

-- Create a table full of test data
create table drop_me
as
select numtodsinterval(dbms_random.random, case mod(abs(trunc(dbms_random.random)), 2)
                                    when 0 then 'SECOND'
                                    when 1 then 'MINUTE' end) x
from dual
connect by level <= 1000000;
 
 
-- First, test it serially.
select sum_ds_interval(x)
from drop_me
where rownum <= 100
;
 
-- Next, test it in parallel
select /*+ parallel(4) */ sum_ds_interval(x)
from drop_me
;
 
-- Cool!  Now drop the test table.  We're done.
drop table drop_me;

The nice thing is, you can do pretty much anything you can do with any other aggregate function with this.  You can even use it as a window function!

select sum_ds_interval(end_job_timestamp - start_job_timestamp) over (partition by job_name) job_duration
from job_runtimes;

I’ve seen other forums, etc, where people get frustrated by Oracle not having this functionality, so I thought I’d share in case someone else experiences my same frustration.

Free Oracle SQL Tuning Guide

Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here: tuningsql.com/secrets