Something I’ve occasionally thought would be really nice is if Oracle had a good way of summing up 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.

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

Usually, I get away with some jankedy code like this:

First, you gotta cast the timestamps as dates (meaning you lose any fractional-seconds).

Then you subtract the start date from the end date.  That 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 sucks, 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.

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” (In oracle 11.2, LISTAGG() was introduced. It offered a lot more flexibility and our home-grown comma-fied lists function became antiquated).

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

Code comes with ABSOLUTELY ZERO guarantees.  Use at your own risk.  Test, test, test the living crap out of it.

Cool! Now we have a SUM_DS_INTERVAL() function we can call.

Let’s test it.

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!

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