Nowadays all DB2 family members support these features and so do most other DBMS vendors. So the name was dropped.
The properties however exist and are ready for usage by anyone who wants to.
Note that there is a hard boundary between these two intervals. That is because you cannot precisely say just how many months a certain number of days make up. Blame the Gregorian Calendar for that one - and the fact that our earth doesn't like round numbers of days per year.
Also note that an INTERVAL is very different from a TIMESTAMP, a DATE or a TIME. These are all moments in time, while intervals describe elapsed time.
UDTs are interesting creatures. They are based on a regular DB2 data type and as such are to some extend just an alias.
In addition however UDTs are also strongly typed. That is you cannot use any functions defined on the base type against the UDT.
The entire library of functions needs to be explicitly defined with the exception of comparison and cast functions.
SET PATH = CURRENT PATH, INTERVAL;
SET SCHEMA = INTERVAL;
CREATE TYPE INTERVAL_DAY_TO_SECOND AS DECIMAL WITH COMPARISONS;
CREATE TYPE INTERVAL_YEAR_TO_MONTH AS DECIMAL WITH COMPARISONS;
CREATE OR REPLACE FUNCTION NUMTODSINTERVAL(val DECFLOAT, unit VARCHAR(10))
RETURNS INTERVAL_DAY_TO_SECOND
RETURN INTERVAL_DAY_TO_SECOND(TRUNC(CASE UPPER(unit)
WHEN 'SECOND' THEN val
WHEN 'MINUTE' THEN val * 60
WHEN 'HOUR' THEN val * 60 * 60
WHEN 'DAY' THEN val * 60 * 60 * 24
ELSE RAISE_ERROR('78000',
'Unknown unit ' || unit || ' for NUMTODSINTERVAL')
END), 9);
CREATE OR REPLACE FUNCTION NUMTOYMINTERVAL(val DECFLOAT, unit VARCHAR(10))
RETURNS INTERVAL_YEAR_TO_MONTH
RETURN INTERVAL_YEAR_TO_MONTH(TRUNC(CASE UPPER(unit)
WHEN 'MONTH' THEN val
WHEN 'YEAR' THEN val * 12
ELSE RAISE_ERROR('78000',
'Unknown unit ' || unit || ' for NUMTOYMINTERVAL')
END));
CREATE OR REPLACE FUNCTION TO_DSINTERVAL(val VARCHAR(20))
RETURNS INTERVAL_DAY_TO_SECOND
RETURN INTERVAL_DAY_TO_SECOND(
CASE WHEN LOCATE_IN_STRING(val, ' ') = 0
THEN DECFLOAT(val) * 60 * 60 * 24
ELSE DECFLOAT(SUBSTR(val, 1, LOCATE_IN_STRING(val, ' ') - 1)) * 60 * 60 * 24
+ (CASE WHEN SUBSTR(val, 1, 1) = '-' THEN -1 ELSE 1 END
* CASE WHEN LOCATE_IN_STRING(val, '.') > 0
THEN MIDNIGHT_SECONDS(TIME(SUBSTR(val,
LOCATE_IN_STRING(val, ' ') + 1,
LOCATE_IN_STRING(val, '.')
- LOCATE_IN_STRING(val, ' ') - 1)))
+ DECFLOAT(RPAD(SUBSTR(val,
LOCATE_IN_STRING(val, '.') + 1,
LENGTH(val) - LOCATE_IN_STRING(val, '.')),
9, '0')) * 0.000000001
ELSE MIDNIGHT_SECONDS(TIME(SUBSTR(val, LOCATE_IN_STRING(val, ' ') + 1,
LENGTH(val) - LOCATE_IN_STRING(val, ' '))))
END)
END);
CREATE OR REPLACE FUNCTION TO_YMINTERVAL(val VARCHAR(20))
RETURNS INTERVAL_YEAR_TO_MONTH
RETURN INTERVAL_YEAR_TO_MONTH(
CASE WHEN SUBSTR(val, 1, 1) = '-'
THEN CASE WHEN LOCATE_IN_STRING(val, '-', 2) > 0
THEN DECFLOAT(SUBSTR(val, 1, LOCATE_IN_STRING(val, '-', 2) - 1)) * 12
- DECFLOAT(SUBSTR(val, LOCATE_IN_STRING(val, '-', 2) + 1,
LENGTH(val) - LOCATE_IN_STRING(val, '-', 2)))
ELSE DECFLOAT(val) * 12 END
ELSE CASE WHEN LOCATE_IN_STRING(val, '-') > 0
THEN DECFLOAT(SUBSTR(val, 1, LOCATE_IN_STRING(val, '-') - 1)) * 12
+ DECFLOAT(SUBSTR(val, LOCATE_IN_STRING(val, '-') + 1,
LENGTH(val) - LOCATE_IN_STRING(val, '-', 2)))
ELSE DECFLOAT(val) END
END);
CREATE OR REPLACE FUNCTION TO_CHAR(val INTERVAL_DAY_TO_SECOND)
RETURNS VARCHAR(29)
RETURN TO_CHAR(TRUNC(DECFLOAT(val) / (60 * 60 * 24)), 'S000000000')
|| TO_CHAR(MOD(TRUNC(ABS(DECFLOAT(val)) / (60 * 60)), 24), '00') || ':'
|| SUBSTR(TO_CHAR(MOD(TRUNC(ABS(DECFLOAT(val)) / 60), 60), '00'), 2) || ':'
|| SUBSTR(TO_CHAR(MOD(TRUNC(ABS(DECFLOAT(val)), 9), 60), '00.000000000'), 2);
CREATE OR REPLACE FUNCTION TO_CHAR(val INTERVAL_YEAR_TO_MONTH)
RETURNS VARCHAR(13)
RETURN TO_CHAR(TRUNC(DECFLOAT(val) / 12), 'S000000000') || '-'
|| SUBSTR(TO_CHAR(MOD(TRUNC(ABS(DECFLOAT(val))), 12), '00'), 2);
CREATE OR REPLACE FUNCTION "+"(INTERVAL_YEAR_TO_MONTH, INTERVAL_YEAR_TO_MONTH)
RETURNS INTERVAL_YEAR_TO_MONTH
SOURCE SYSIBM."+"(DECFLOAT, DECFLOAT);
CREATE OR REPLACE FUNCTION "-"(INTERVAL_YEAR_TO_MONTH, INTERVAL_YEAR_TO_MONTH)
RETURNS INTERVAL_YEAR_TO_MONTH
SOURCE SYSIBM."-"(DECFLOAT, DECFLOAT);
-- Put DATE before TIMESTAMP. In ORA mode the later will override DATE
CREATE OR REPLACE FUNCTION "+"(arg1 DATE, arg2 INTERVAL_YEAR_TO_MONTH)
RETURNS DATE
NO EXTERNAL ACTION CONTAINS SQL DETERMINISTIC
RETURN arg1 + DECFLOAT(arg2) MONTHS;
CREATE OR REPLACE FUNCTION "-"(arg1 DATE, arg2 INTERVAL_YEAR_TO_MONTH)
RETURNS DATE
NO EXTERNAL ACTION CONTAINS SQL DETERMINISTIC
RETURN arg1 - DECFLOAT(arg2) MONTHS;
CREATE OR REPLACE FUNCTION "+"(arg1 TIMESTAMP(12), arg2 INTERVAL_YEAR_TO_MONTH)
RETURNS TIMESTAMP(12)
NO EXTERNAL ACTION CONTAINS SQL DETERMINISTIC
RETURN arg1 + DECFLOAT(arg2) MONTHS;
CREATE OR REPLACE FUNCTION "-"(arg1 TIMESTAMP(12), arg2 INTERVAL_YEAR_TO_MONTH)
RETURNS TIMESTAMP(12)
NO EXTERNAL ACTION CONTAINS SQL DETERMINISTIC
RETURN arg1 - DECFLOAT(arg2) MONTHS;
CREATE OR REPLACE FUNCTION "+"(arg1 INTERVAL_YEAR_TO_MONTH, arg2 DATE)
RETURNS DATE
NO EXTERNAL ACTION CONTAINS SQL DETERMINISTIC
RETURN arg2 + DECFLOAT(arg1) MONTHS;
CREATE OR REPLACE FUNCTION "+"(arg1 INTERVAL_YEAR_TO_MONTH, arg2 TIMESTAMP(12))
RETURNS TIMESTAMP(12)
NO EXTERNAL ACTION CONTAINS SQL DETERMINISTIC
RETURN arg2 + DECFLOAT(arg1) MONTHS;
CREATE OR REPLACE FUNCTION "+"(INTERVAL_DAY_TO_SECOND, INTERVAL_DAY_TO_SECOND)
RETURNS INTERVAL_DAY_TO_SECOND
SOURCE SYSIBM."+"(DECFLOAT, DECFLOAT);
CREATE OR REPLACE FUNCTION "-"(INTERVAL_DAY_TO_SECOND, INTERVAL_DAY_TO_SECOND)
RETURNS INTERVAL_DAY_TO_SECOND
SOURCE SYSIBM."-"(DECFLOAT, DECFLOAT);
CREATE OR REPLACE FUNCTION "+"(arg1 TIMESTAMP(12), arg2 INTERVAL_DAY_TO_SECOND)
RETURNS TIMESTAMP(12)
NO EXTERNAL ACTION CONTAINS SQL DETERMINISTIC
RETURN arg1 + DECFLOAT(arg2) SECONDS;
CREATE OR REPLACE FUNCTION "-"(arg1 TIMESTAMP(12), arg2 INTERVAL_DAY_TO_SECOND)
RETURNS TIMESTAMP(12)
NO EXTERNAL ACTION CONTAINS SQL DETERMINISTIC
RETURN arg1 - DECFLOAT(arg2) SECONDS;
CREATE OR REPLACE FUNCTION "+"(arg1 INTERVAL_DAY_TO_SECOND, arg2 TIMESTAMP(12))
RETURNS TIMESTAMP(12)
NO EXTERNAL ACTION CONTAINS SQL DETERMINISTIC
RETURN arg2 + DECFLOAT(arg1) SECONDS;