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.
23c though has, thankfully, made those functions obsolete.
As a quick recap for comparison…
select dsintervalsum(result) odci_pre_23c 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_23C
--------------------------------------------------
+000000004 00:22:17.154024000
And in 23c we can use SUM just as we would with numeric types.
select sum(result) new_23c 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_23C
--------------------------------------------------
+000000004 00:22:17.154024300
Similarly, SUM can be used with year to month as well.
SELECT ymintervalsum(result) odci_pre_23c
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_23C
--------------------------------------------------
+24-02
And again, the SUM function can be used on the year to month intervals.
SELECT sum(result) new_23c
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_23C
--------------------------------------------------
+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. 23c has extended the native AVG function to include support for these interval types.
select avg(result) new_23c 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_23C
--------------------------------------------------
+000000001 00:05:34.288506075
SELECT avg(result) new_23c
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_23C
--------------------------------------------------
+06-00
While there is nothing wrong with the user-defined aggregates, I recommend using the native functions when using a database of version 23c 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. 23c is a great expansion on top of the already robust functionality. Questions and comments, as always, are welcomed.