An INTERVAL YEAR TO MONTH, can have up to 9 digits of year precision. By default, a column or pl/sql variable will have 2 digits. The month portion of the interval is always limited to 2 digits. The year limits can be changed by specifying (0-9) in the declaration.
select INTERVAL '123-06' YEAR(3) TO MONTH from dual;
DECLARE
i INTERVAL YEAR(5) TO MONTH;
BEGIN
i := TO_YMINTERVAL('12345-00');
DBMS_OUTPUT.put_line(i);
END;
As mentioned in the overview , INTERVALS do not represent any particular period of time.
Literals
Intervals have a variety of ways of being created. In the examples above I show one method of using an interval-literal and in the other I use a function. Interval-literals come in the form of the key word INTERVAL followed by either a number and a unit or a duration format followed by the range of units supplied. In either case, the units may optionally include precision limits . Templates of each would look like the following:
INTERVAL ‘m’ MONTH
INTERVAL ‘y’ YEAR
INTERVAL ‘y-m’ YEAR TO MONTH
Where y and m are integers. When used in the “y-m” form, “m” is restricted to the range 0 to 11. If you want to specify 12 or more months in the “y-m” form, then adjust the “y” year value accordingly. The YEAR keyword may optionally include precision values. When specifying only months, you can use integers larger than 11 or smaller than -11 and the years and months will be automatically calculated. For negative intervals in the “y-m” form, the sign is applied to the year; the “-” character in the middle is a delimiter, not a minus sign.
SELECT INTERVAL '3' YEAR,
INTERVAL '12' MONTH,
INTERVAL '-37' MONTH,
INTERVAL '3-11' YEAR TO MONTH,
INTERVAL '-4-10' YEAR TO MONTH,
INTERVAL '123-06' YEAR(3) TO MONTH,
INTERVAL '0-5' YEAR(0) TO MONTH
FROM DUAL;
As with INTERVAL DAY TO SECOND, even though the “y” and “m” values are numeric, the interval-literal format requires them to be entered as character strings. Also, since these are literals they may not include bind variables for the character strings.
Functions
In addition to creating intervals with literals, Oracle also provides functions. Using the functions allows for passing variables or referencing column values. The functions are NUMTOYMINTERVAL and TO_YMINTERVAL.
NUMTOYMINTERVAL is fairly straight forward. It takes two parameters: a number, and a character string of one of the unit names: ‘YEAR’ or ‘MONTH’. It will return an INTERVAL YEAR(9) TO MONTH filled with the appropriate number of units, rolling them across the various fields of the interval as needed to canonicalize them into years and 0-11 months. Also note, both year and month values of the resulting INTERVAL are always integers. If the input-year value is not an integer, the fractional portion will be converted to months where one month is 1/12th of a year and rounded to the nearest integer. If the input-month value is not an integer, it will be rounded to the nearest integer and then returned as a whole number within the final interval.
SQL> select numtoyminterval(10,'year') result from dual 2 union all 3 select numtoyminterval(37,'month') result from dual 4 union all 5 select numtoyminterval(3.4,'year') result from dual 6 union all 7 select numtoyminterval(33.7,'month') from dual; RESULT +000000010-00 +000000003-01 +000000003-05 +000000002-10
TO_YMINTERVAL supports two input formats (since 11gR1.) A SQL format and an ISO 8601 format. Both formats consist of a single character string. In 10gR2 and lower a second parameter to specify NLS characters was supported. The syntax is still accepted in 11.1 and above, but the parameter is ignored.
The SQL format has only one form: TO_YMINTERVAL(‘y-m’). As with “y-m” literal forms the month “m” value must fall within 0-11.
SQL> select TO_YMINTERVAL('1-3') sql_format from dual;
SQL_FORMAT
+000000001-03
The ISO 8601 format is identified with a P or -P (for period) as the leading characters. It is not legal syntax to include a + character for positive intervals, only negative intervals include a sign. After the P each component of years, months, days, hours, minutes, and/or seconds may be included in order of descending magnitude with Y, M, D, H, M, and S as suffixes to follow the numeric values. However, while the function will support any of those values, only the year and month portions are used. All other values, will be ignored, but still must follow legal syntax.
Thus the legal format templates (not counting the optional minus sign) that can produce non-zero values are limited to…
PnYnM -- years and months PnY -- years only PnM -- months only
While it is permissible to include the formats for days, hours, minutes, and seconds supported by TO_DSINTERVAL all of them will be ignored.
Again, the “n” stands for a numeric value for the suffixed units of years and months. Where the SQL model describes an interval in terms of its total amount of time in canonicalized clock-time scale, the ISO model is a summation of each time component. Thus the ISO model does not require months to be 0-1. Any positive integer can be used for year and month values. The function will sum the values and roll the values up to into larger units as needed. Thus TO_YMINTERVAL(‘P99M’) will return an interval of 99 months which is 8 years and 3 months.
SQL> select TO_YMINTERVAL('P99M') from dual;
TO_YMINTERVAL('P99M')
+000000008-03
The ISO format supports multiple such additions. In the following example 10 years and 99 months are added together to create a single interval of 18 years and 3 months.
SQL> select to_yminterval('P10Y99M') from dual;
TO_YMINTERVAL('P10Y99M')
+000000018-03
The following example shows the days, hours, minutes, and seconds all being ignored so the resulting interval is 0 years and 0 months.
SQL> select to_yminterval('P10DT50H99M1000S') result from dual;
RESULT
+000000000-00
Similarly, if you have year or month values in your ISO format, those will be summed while the days, hours, minutes, and seconds will be ignored, adding nothing. Here I’ve combined the 10 year and 99 month example with the ignored value example and the resulting sum is still an interval of 18 years and 3 months.
SQL> select to_yminterval('P10Y99M10DT50H99M1000S') from dual;
TO_YMINTERVAL('P10Y99M10DT50H99M1000S')
+000000018-03
Timestamp Math
The default type for subtracting a timestamp from another timestamp or a date is INTERVAL DAY TO SECOND. If you want an INTERVAL YEAR TO MONTH result, you must declare it as a modifier to the subtraction expression. As with the literal and functions, any partial months will be rounded. If the result will have more than 2 digits of years then the precision will need to be specified in the modifier.
SQL> SELECT (TIMESTAMP '2020-05-25 12:34:56' - TIMESTAMP '2019-11-15 00:00:00') YEAR TO MONTH result FROM DUAL; RESULT +00-06 SQL> SELECT (TIMESTAMP '2020-05-25 12:34:56' - TIMESTAMP '1819-11-15 00:00:00') YEAR(3) TO MONTH result FROM DUAL; RESULT +200-06
In addition to creating interval through subtraction, you can add intervals to a DATE or TIMESTAMP to create a new DATE or TIMESTAMP value of the same type. The resulting TIMESTAMPs will always have 9 digits of precision, regardless of the original TIMESTAMP’s precision.
SQL> SELECT DATE '2020-12-20' + INTERVAL '3-10' YEAR TO MONTH result FROM DUAL; RESULT 2024-10-20 00:00:00 SQL> SELECT TIMESTAMP '2020-12-20 00:00:00' + INTERVAL '3-10' YEAR TO MONTH result FROM DUAL; RESULT 2024-10-20 00:00:00.000000000 SQL> SELECT TIMESTAMP '2020-12-20 00:00:00 US/Eastern' + INTERVAL '3-10' YEAR TO MONTH result FROM DUAL; RESULT 2024-10-20 01:00:00.000000000 US/EASTERN
INTERVAL YEAR TO MONTH date math can be a little tricky because it will attempt to construct exactly the day of the month from the math, even if that date does not exist. For example Dec 31 + 2 months would be February 31 which is not a legal date. This is different from date math with the ADD_MONTHS function which would adjust the final result for out-of-range values to always be the last day of the month if needed. Errors will also happen if you add months or years to February 29 and the result would be in a non-leap year. Thus, all of the following will fail with ORA-1839 errors.
select date '2020-12-31' + interval '2' month from dual; select date '2020-12-31' - interval '1' month from dual; select date '2020-02-29' + interval '12' month from dual; select date '2020-02-29' + interval '1' year from dual; ORA-01839: date not valid for month specified
23ai Aggregate functions
Beginning with 23ai, Oracle includes aggregate functions for INTERVAL types.
I wrote a separate article about them here.
User-Defined Aggregates
Prior to 23ai however, Oracle does not support a SUM aggregate for INTERVAL types. However, since 9i we have been able to define our own aggregates using the Data Cartridge API. Using this as a model you could extend to MIN, MAX, and AVG aggregates as well.
CREATE OR REPLACE TYPE yminterval_sum_type AS OBJECT
(
v_result INTERVAL YEAR(9) TO MONTH, -- our working result
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT yminterval_sum_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate(
self IN OUT yminterval_sum_type,
p_interval IN INTERVAL YEAR TO MONTH)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge(self IN OUT yminterval_sum_type,
ctx2 IN yminterval_sum_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate(
self IN yminterval_sum_type,
returnvalue OUT INTERVAL YEAR TO MONTH,
flags IN NUMBER)
RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY yminterval_sum_type
IS
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT yminterval_sum_type)
RETURN NUMBER
IS
BEGIN
ctx := yminterval_sum_type(NULL); -- initialize the sum to NULL
RETURN odciconst.success;
END odciaggregateinitialize;
MEMBER FUNCTION odciaggregateiterate(
self IN OUT yminterval_sum_type,
p_interval IN INTERVAL YEAR TO MONTH)
RETURN NUMBER
IS
BEGIN
IF self.v_result IS NULL
THEN
self.v_result := p_interval; -- the first interval is the sum
ELSE
self.v_result := (self.v_result + p_interval); -- add each new interval to the running total
END IF;
RETURN odciconst.success;
END odciaggregateiterate;
MEMBER FUNCTION odciaggregatemerge(self IN OUT yminterval_sum_type,
ctx2 IN yminterval_sum_type)
RETURN NUMBER
IS
BEGIN
IF self.v_result IS NULL
THEN
self.v_result := ctx2.v_result; -- If our sum is NULL, use the result of the other set
ELSIF ctx2.v_result IS NOT NULL
THEN
self.v_result := (self.v_result + ctx2.v_result); -- If both sets have sums, add them to get the total
END IF;
RETURN odciconst.success;
END odciaggregatemerge;
MEMBER FUNCTION odciaggregateterminate(
self IN yminterval_sum_type,
returnvalue OUT INTERVAL YEAR TO MONTH,
flags IN NUMBER)
RETURN NUMBER
IS
BEGIN
returnvalue := self.v_result; -- the iterate and merge did all the work, return the result
RETURN odciconst.success;
END odciaggregateterminate;
END;
/
Using the ODCI type above, you can then define an aggregate function to use it.
CREATE OR REPLACE FUNCTION ymintervalsum(p_interval yminterval_unconstrained)
RETURN yminterval_unconstrained
PARALLEL_ENABLE
AGGREGATE USING yminterval_sum_type;
Once you have the function, you can use it anywhere you’d use a native aggregate.
SQL> SELECT ymintervalsum(result)
2 FROM (SELECT NUMTOYMINTERVAL(3.4, 'year') result FROM DUAL
3 UNION ALL
4 SELECT NUMTOYMINTERVAL(3.8, 'month') FROM DUAL
5 UNION ALL
6 SELECT TO_YMINTERVAL('P10Y99M') FROM DUAL
7 UNION ALL
8 SELECT INTERVAL '2-2' YEAR TO MONTH FROM DUAL);
YMINTERVALSUM(RESULT)
+000000024-02