## Forum Stats

• 3,824,969 Users
• 2,260,446 Discussions

Discussions

# SUM on INTERVAL

Member Posts: 10,535 Gold Crown
edited May 7, 2020 5:53PM

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

82

#### Under Review - Voting Still Open · Last Updated May 7, 2020 5:53PM

«1

• Member Posts: 3,666 Gold Crown

So you want to do something like this?

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

from blah

• Member Posts: 9,567 Bronze Crown

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.

• Member Posts: 511

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.

• Member Posts: 10,535 Gold Crown

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.

• Member Posts: 9,567 Bronze Crown

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.

• Member Posts: 511
`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).

• Member Posts: 10,535 Gold Crown

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.

• edited Jan 20, 2017 2:15PM

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`.`
• Member Posts: 9,567 Bronze Crown

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