SUM on INTERVAL

Sven W.

I'd like the aggregate function SUM (and its analytic counterpart) also work with INTERVAL datatypes, mostly for the Day to Second interval.

William Robertson

Totally agree - how did Oracle forget this for (so far) two major releases? It makes INTERVAL columns useless because you can't add them up, even though you can add them together.

Not just SUM either - we need AVG, MEDIAN and the rest of the standard aggregate/analytic functions, so we can stop having to write our own.

Edit, 2018-03-06: The Oracle 18c documentation is now available, and Oracle has still have not got around to fixing this. That makes 3 major releases with this trivial oversight unfixed. Oracle, what is taking so long? If you are having trouble you can use mine. Each function is about 70 lines of code. I wrote them in 2004.

I mentioned this on Twitter, half-jokingly, and Connor McDonald's reply was to ask me whether interval aggregation is in the ANSI standard. Well, I checked and it is (to the extent that anything is in the ANSI standard). And, Postgres supports it.

Edit, 2021: How about I come back here every two or three major releases and moan about this trivial limitation. In 21c I still can't

select sum(interval '1' minute) from dual;

even though I have been able to

select interval '1' minute + interval '1' second from dual;

since the early 2000's.

top.gun

So you want to do something like this?

select SUM(time1-time0) day(4) to second(0)

from blah

William Robertson

So you want to do something like this?

select SUM(time1-time0) day(4) to second(0)

from blah

I think we'd be happy with

sum(i)

where i is an interval column, or

sum(t2 - t1)

where t1 and t2 are timestamps.

Raj Jamadagni

I'd be happy if they provide cast(interval as seconds)  so i can avoid multiple extract() and then adding them us every time. Once you an cast it as number, other operations become easy.

Sven W.

I'd be happy if they provide cast(interval as seconds)  so i can avoid multiple extract() and then adding them us every time. Once you an cast it as number, other operations become easy.

Raj Jamadagni wrote:I'd be happy if they provide cast(interval as seconds) so i can avoid multiple extract() and then adding them us every time. Once you an cast it as number, other operations become easy.

It is a bit cumbersome, but it is possible without EXTRACT and adding values.

For example if we have a interval of 2min:15sec, we can convert it into number like this:

to_number(to_char(trunc(sysdate,'YEAR') + interval '2:15' minute to second,'SSSSS'))135

This works as long as the number of seconds is less than a day.

William Robertson

I'd be happy if they provide cast(interval as seconds)  so i can avoid multiple extract() and then adding them us every time. Once you an cast it as number, other operations become easy.

Raj Jamadagni wrote:I'd be happy if they provide cast(interval as seconds) so i can avoid multiple extract() and then adding them us every time. Once you an cast it as number, other operations become easy.

I'm all for extensions to improve the functionality of interval types, however cast(interval as seconds) would require a datatype called 'seconds'. I think it would make more sense to overload the existing extract() function for intervals.

Agreed, operations on numbers (sum, average, median, standard deviation etc) are much easier - however you then generally want to convert it back again.

Raj Jamadagni

Raj Jamadagni wrote:I'd be happy if they provide cast(interval as seconds) so i can avoid multiple extract() and then adding them us every time. Once you an cast it as number, other operations become easy.

I'm all for extensions to improve the functionality of interval types, however cast(interval as seconds) would require a datatype called 'seconds'. I think it would make more sense to overload the existing extract() function for intervals.

Agreed, operations on numbers (sum, average, median, standard deviation etc) are much easier - however you then generally want to convert it back again.

Thanks William, I should have said, cast(interval as number) instead of seconds, then you can convert it back as well cast(number  as interval).

Sven W.

Thanks William, I should have said, cast(interval as number) instead of seconds, then you can convert it back as well cast(number  as interval).

To convert a number into an intervall we can do already. The functions to do so are:

NUMTODSINTERVAL and NUMTOYMINTERVAL.

Unknown

Totally agree - how did Oracle forget this for (so far) two major releases? It makes INTERVAL columns useless because you can't add them up, even though you can add them together.

Not just SUM either - we need AVG, MEDIAN and the rest of the standard aggregate/analytic functions, so we can stop having to write our own.

Edit, 2018-03-06: The Oracle 18c documentation is now available, and Oracle has still have not got around to fixing this. That makes 3 major releases with this trivial oversight unfixed. Oracle, what is taking so long? If you are having trouble you can use mine. Each function is about 70 lines of code. I wrote them in 2004.

I mentioned this on Twitter, half-jokingly, and Connor McDonald's reply was to ask me whether interval aggregation is in the ANSI standard. Well, I checked and it is (to the extent that anything is in the ANSI standard). And, Postgres supports it.

The OCI functionality already includes functions to convert intervals to numbers and back again.

https://docs.oracle.com/database/121/LNOCI/oci19map002.htm#LNOCI17436

OCIIntervalFromNumber()

PurposeConverts an Oracle NUMBER to an interval.. . .

OCIIntervalToNumber()

PurposeConverts an interval to an Oracle NUMBER.

William Robertson

The OCI functionality already includes functions to convert intervals to numbers and back again.

https://docs.oracle.com/database/121/LNOCI/oci19map002.htm#LNOCI17436

OCIIntervalFromNumber()

PurposeConverts an Oracle NUMBER to an interval.. . .

OCIIntervalToNumber()

PurposeConverts an interval to an Oracle NUMBER.

Well that's fascinating for C programmers I guess, but SQL can already add two intervals together without conversion or OCI functions, so supporting sum() etc is purely a matter of Oracle's SQL team bothering to overload the relevant aggregate functions to do this for each value (which is how you write your own).