Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 75 Insurance
- 537.7K On-Premises Infrastructure
- 138.7K Analytics Software
- 38.6K Application Development Software
- 6.1K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.3K Infrastructure Software
- 105.4K Integration
- 41.6K Security Software
SUM on INTERVAL

I'd like the aggregate function SUM (and its analytic counterpart) also work with INTERVAL datatypes, mostly for the Day to Second interval.
Comments
-
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.
-
So you want to do something like this?
select SUM(time1-time0) day(4) to second(0)
from blah
-
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.
-
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'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.
-
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 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).
-
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.
-
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
. -
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).