I recently got asked whether DB2 support the INTERVAL data types.
My answer was, no, it does not. But DB2 does support so called "labeled durations"!
Labeled durations are a transient type that can only appear when you are doing datetime arithmetic.
An example for a labeled duration is: "7 months".
VALUES CURRENT TIMESTAMP + 7 MONTHS
Adds seven months to the current moment in time.
Now, labeled durations predate the SQL Standard and they do have the downside that you cannot store them in a column.
You can only store the values themselves in a column not the fact that they are durations.
The question arises whether DB2 can be extended to a proper interval date type.
Remember that we used to call DB2 "UDB" (Universal Database)?
Aside from the fact that "Universal" was a fashion word at the time (I think Informix started it ;-) there was actually meaning attached to that name.
A DB2 could call itself UDB when it supported:
- User Defined Functions (UDF)
- Large OBjects (LOB)
- and User defined, Distinct Types (UDT)
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.
So In the following scripts we will:
- Use UDTs to define two new types:
An interval that hold a multiple of months counted in years an months
An interval that folds multiples of seconds counted in fractions of seconds all the way up to days.
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.
That leads us to...
- Use UDF to define operations on intervals.
There aren't terribly many things one can do to intervals. Here is a list:
- NUMTODSINTERVAL(<number>, <unit>) => DAY_TO_SECOND_INTERVAL
This function takes a numeric argument and turns into the DAY_TO_SECOND_INTERVAL.
The unit can be a string such as 'DAY', 'second', or 'Minute'.
It is fine to use fractions.
- NUMTOYMINTERVAL(<number>, <unit>) => YEAR_TO_MONTH_INTERVAL
This function takes a number and turns it into a YEAR_TO_MONTH_INTERVAL.
The unit can be a string such as 'Year', or 'month'.
It is fine to use fractions, fractional months are truncated however.
- TO_DSINTERVAL(<string>) => DAY_TO_SECOND_INTERVAL
This function turns a string into a DAY_TO_SECOND_INTERVAL.
The string has be of the format DD9 HH:MI:SS[.FF9]
DD9 means you can specify up to 9 digits for the days. Also you can use + or - to denote positive or negative intervals.
The fractional seconds are optional and can go down to nano seconds.
- TO_YMINTERVAL(<string>) => YEAR_TO_MONTH_INTERVAL
This function turns a string into a YEAR_TO_MONTH_INTERVAL.
The string has to be of the format: YY9-MM
YY9 means you can specify up to 9 digits for the year. Also you can use + and - to denote positive or negative intervals.
Note that DB2 does not validate that MM is less than 12.
- TO_CHAR(<dtsinterval>) => VARCHAR(29)
This function formats a DAY_TO_SECOND_INTERVAL value into a string.
The format is of the form: SDD9 HH:MI:SS.FF9
S is the plus or minus sign
=> -000000002 05:49:05.120000000
=> +000000023 08:27:21.600000000
This function formats a YEAR_TO_MONTH_INTERVAL.
The format is of the form: SYY9-MM
S is the plus or minus sign
- <timestamp> + <dtsinterval>
<dtsinterval> + <timestamp>
Adds an day to second interval to a timestamp.
TIMESTAMP '2010-10-30 18:16:00.123' + TO_DSINTERVAL('12 07:12:30')
TIMESTAMP '2010-10-30 18:16:00.123' + TO_DSINTERVAL('-12 07:12:30')
- <time_stamp> - <dtsinterval>
Substracts a day to second interval from a timestamp
TIMESTAMP '2010-10-30 18:16:00.123' - TO_DSINTERVAL('12 07:12:30')
- <date> + <ytminterval>
<timestamp> + <ytminterval>
<ytminterval> + <date>
<ytminterval> + <timestamp>
Adds a year to month interval to a date or timestamp
DATE'2010-10-30' + NUMTOYMINTERVAL(23, 'MONTH')
TIMESTAMP'2010-10-30 18:16:00.123' + TO_YMINTERVAL('80-02')
- <date> - <ytminterval>
<timestamp> - <ytminterval>
Substracts a year to month interval from a date or timestamp
DATE'2010-10-30' - TO_YMINTERVAL('11-05')
TIMESTAMP'2010-10-30 18:16:00.123' - TO_YMINTERVAL('80-02')
- <dtsinterval> + <dtsinterval>
<dtsinterval> - <dtsinterval>
Adds or substracts two day to second intervals
TO_CHAR(TO_DSINTERVAL('45 10:00:12.123') + NUMTODSINTERVAL(88, 'SECOND'))
=> +000000045 10:01:40.123000000
TO_CHAR(NUMTODSINTERVAL(14.5, 'DAY') - TO_DSINTERVAL('13 12:00:00'))
=> +000000001 00:00:00.000000000
- <ytminterval> + <ytminterval>
<ytminterval> - <ytminterval>
Adds or substracts two year to month intervals
TO_CHAR(TO_YMINTERVAL('-30-03') + NUMTOYMINTERVAL(3.25, 'YEAR'))
TO_CHAR(TO_YMINTERVAL('100-00') - NUMTOYMINTERVAL(3, 'MONTH'))
- =, != (<>), <, >, <=, >= as well as BETWEEN and IN are also supported for both YEAR_TO_MONTH_INTERVAL and DAY_TO_SECOND_INTERVAL
Here is the definition of the 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))
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
'Unknown unit ' || unit || ' for NUMTODSINTERVAL')
CREATE OR REPLACE FUNCTION NUMTOYMINTERVAL(val DECFLOAT, unit VARCHAR(10))
RETURN INTERVAL_YEAR_TO_MONTH(TRUNC(CASE UPPER(unit)
WHEN 'MONTH' THEN val
WHEN 'YEAR' THEN val * 12
'Unknown unit ' || unit || ' for NUMTOYMINTERVAL')
CREATE OR REPLACE FUNCTION TO_DSINTERVAL(val VARCHAR(20))
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
LOCATE_IN_STRING(val, ' ') + 1,
- LOCATE_IN_STRING(val, ' ') - 1)))
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