Twitter Linkedin RSS
Close
Oracle

Oracle 23ai interval aggregates

Sean D. StuberLeave a comment

A few years ago I wrote a couple articles about Day to Second and Year to Month intervals. In those articles I included examples of user-defined aggregates allowing you to sum the corresponding interval values.

23ai (formerly known as 23c) though has, thankfully, made those functions obsolete.

As a quick recap for comparison…

select dsintervalsum(result) odci_pre_23ai from
    (
    select numtodsinterval(3.8522463,'day') result from dual
    union all
    select numtodsinterval(3.8522463,'hour') from dual
    union all
    select numtodsinterval(3.8522463,'minute') from dual
    union all
    select numtodsinterval(3.8522463,'second') from dual);

ODCI_PRE_23AI                                      
--------------------------------------------------
+000000004 00:22:17.154024000

And in 23ai we can use SUM just as we would with numeric types.

select sum(result) new_23ai from
    (
    select numtodsinterval(3.8522463,'day') result from dual
    union all
    select numtodsinterval(3.8522463,'hour') from dual
    union all
    select numtodsinterval(3.8522463,'minute') from dual
    union all
    select numtodsinterval(3.8522463,'second') from dual);

NEW_23AI                                         
--------------------------------------------------
+000000004 00:22:17.154024300

Similarly, SUM can be used with year to month as well. 

SELECT ymintervalsum(result) odci_pre_23ai
  FROM (SELECT NUMTOYMINTERVAL(3.4, 'year') result FROM DUAL
        UNION ALL
        SELECT NUMTOYMINTERVAL(3.8, 'month') FROM DUAL
        UNION ALL
        SELECT TO_YMINTERVAL('P10Y99M') FROM DUAL
        UNION ALL
        SELECT INTERVAL '2-2' YEAR TO MONTH FROM DUAL);

ODCI_PRE_23AI                                    
--------------------------------------------------
+24-02

And again, the SUM function can be used on the year to month intervals.

SELECT sum(result) new_23ai
  FROM (SELECT NUMTOYMINTERVAL(3.4, 'year') result FROM DUAL
        UNION ALL
        SELECT NUMTOYMINTERVAL(3.8, 'month') FROM DUAL
        UNION ALL
        SELECT TO_YMINTERVAL('P10Y99M') FROM DUAL
        UNION ALL
        SELECT INTERVAL '2-2' YEAR TO MONTH FROM DUAL);

NEW_23AI                                        
--------------------------------------------------
+24-02

In my previous articles I noted the ODCI SUM example could be used as a model for building other aggregates, such as calculating an average. 23ai has extended the native AVG function to include support for these interval types.

select avg(result) new_23ai from
    (
    select numtodsinterval(3.8522463,'day') result from dual
    union all
    select numtodsinterval(3.8522463,'hour') from dual
    union all
    select numtodsinterval(3.8522463,'minute') from dual
    union all
    select numtodsinterval(3.8522463,'second') from dual);

NEW_23AI                                           
--------------------------------------------------
+000000001 00:05:34.288506075

    
SELECT avg(result) new_23ai
  FROM (SELECT NUMTOYMINTERVAL(3.4, 'year') result FROM DUAL
        UNION ALL
        SELECT NUMTOYMINTERVAL(3.8, 'month') FROM DUAL
        UNION ALL
        SELECT TO_YMINTERVAL('P10Y99M') FROM DUAL
        UNION ALL
        SELECT INTERVAL '2-2' YEAR TO MONTH FROM DUAL); 

NEW_23AI                                         
--------------------------------------------------
+06-00

While there is nothing wrong with the user-defined aggregates, I recommend using the native functions when using a database of version 23ai or higher. Of course, if you are using a version between 9i and 21c, then by all means continue to use the ODCI functionality.

This is another simple, but appreciated improvement to the Oracle SQL syntax. 23ai is a great expansion on top of the already robust functionality. Questions and comments, as always, are welcomed.

Leave a Reply