Forum Stats

  • 3,824,969 Users
  • 2,260,446 Discussions
  • 7,896,363 Comments

Discussions

SUM on INTERVAL

Sven W.
Sven W. Member Posts: 10,535 Gold Crown
edited May 7, 2020 5:53PM in Database Ideas - Ideas

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

Manish ChaturvediUser259623 -OracleulohmannmarkmevansPrasad.Gadiraju-Oraclevinaykumar2rohanwaliaArpit Jain -OracleHari Meena-OracleAparna Dutta-OracleSven W.Sathees -OraclecaadecarvalhoPravin TakpireCarsten KaftanRafuctriebRajenBLothar Flatzfac586JagadekarabhagatsinghWilliam RobertsonBrian Bakulatop.gunKiran PawarGeert GruwezsysassysdbaKim Berg HansenChris HuntPkKayKuser12182396gaverillGalo BaldatmbeetzApexBineBPeaslandDBAKevan Gelling1483608ParsRob van WijkHarbourGhostTom vd DuinRichard Smith5c6e4cec-787c-4a56-a5ea-4a1afce715d1rvstuckesensoftNikolaus ThielMartin PreissmathguyberxMike Kutz-KE-MsJJeffrey KempThorsten KettnercormacoLukas EderTony AndrewsNenad NoveljicNiels HeckergassenmjJustin WarwickHemant K Chitaleblessed DBAStew AshtonPiotr WrzosekMustafa_KALAYCIPeter HraškoUser_2KT88BlackBraneAlex NuijtenKthunSentinelJohn_KBob JarvisWernfriedconnor_mc_d-OraclesdstuberUser_930JVMarkStewartuser11198823
84 votes

Under Review - Voting Still Open · Last Updated

«1

Comments

  • top.gun
    top.gun 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

  • William Robertson
    William Robertson 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.

    top.gun5c6e4cec-787c-4a56-a5ea-4a1afce715d1MarkStewart
  • Raj Jamadagni
    Raj Jamadagni 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.

  • Sven W.
    Sven W. 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.

  • William Robertson
    William Robertson 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.

    Raj JamadagniThorsten Kettner
  • 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.
    Sven W. 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.

  • Unknown
    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.
  • William Robertson
    William Robertson 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).

    Bob Jarvis