An INTERVAL DAY TO SECOND, can have up to 9 digits of sub-second precision (nanoseconds.) By default, a column or pl/sql variable will have 6 digits (microseconds.) In addition to the subsecond precision, a default INTERVAL DAY TO SECOND will also be limited to 2 digits in the day counter. These limits can be changed by specifying the limit of each (0-9) in the declaration.
select interval '123456789 00:00:00.123456789' day(9) to second(9) from dual;
DECLARE
i INTERVAL DAY(9) TO SECOND(9);
BEGIN
i := TO_DSINTERVAL('123456789 00:00:00.123456789');
DBMS_OUTPUT.put_line(i);
END;
As mentioned in the overview, INTERVALS do not represent any particular period of time. Thus time zones and daylight saving time are not supported within an INTERVAL DAY TO SECOND. So one day within an interval is always 24 hours.
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 'd' DAY INTERVAL 'h' HOUR INTERVAL 'm' MINUTE INTERVAL 's' SECOND INTERVAL 'd h:m:s' DAY TO SECOND INTERVAL 'd h:m' DAY TO MINUTE INTERVAL 'd h' DAY TO HOUR INTERVAL 'h:m:s' HOUR TO SECOND INTERVAL 'h:m' HOUR TO MINUTE INTERVAL 'm:s' MINUTE TO SECOND
Where “d”, “h”, and “m” are integers, “s” can be a decimal number. When used in the duration form, if a higher unit is included in the duration range then the time components are restricted to normal clock ranges. For example “h” is restricted to the range 0-23 when days are included in the format. “m” is restricted to 0-59 if hours or days are in the format. “s” is restricted to 0-59.999999999 or the upper limit of the precision specified for seconds if hours, minutes, or days are included.
Another oddity of literal precision is how it interacts with the day counter.
If you want to restrict the interval to only having 3 digits of days, you can specify DAY(3), but if your units or duration do not include days then the precision is applied to the largest unit that is included. If you are only specifying a number of seconds then you should include the precision of both the day and the fractional portions in the precision, separated by a comma. Thus the formats above might be written to include precision as follows:
INTERVAL 'd' DAY(3) INTERVAL 'h' HOUR(3) INTERVAL 'm' MINUTE(3) INTERVAL 's' SECOND(3,5) INTERVAL 'd h:m:s' DAY(3) TO SECOND(5) INTERVAL 'd h:m' DAY(3) TO MINUTE INTERVAL 'd h' DAY(3) TO HOUR INTERVAL 'h:m:s' HOUR(3) TO SECOND(5) INTERVAL 'h:m' HOUR(3) TO MINUTE(5) INTERVAL 'm:s' MINUTE(3) TO SECOND(5)
Even though the “d”, “h”, “m”, and “s” values are all 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. The query below, includes an example of each of the formats described above.
SELECT INTERVAL '7 2:7:38.123456789' DAY TO SECOND(9),
INTERVAL '1' DAY,
INTERVAL '1' HOUR,
INTERVAL '1' MINUTE,
INTERVAL '1' SECOND,
INTERVAL '1234.2345' SECOND(3, 4),
INTERVAL '1 2:3:4.567' DAY TO SECOND,
INTERVAL '1 2:3' DAY TO MINUTE,
INTERVAL '1 2' DAY TO HOUR,
INTERVAL '1:2:3.45' HOUR TO SECOND,
INTERVAL '1:2' HOUR TO MINUTE,
INTERVAL '1:2.345' MINUTE TO SECOND,
INTERVAL '1' DAY(3),
INTERVAL '1' HOUR(3),
INTERVAL '1' MINUTE(3),
INTERVAL '1' SECOND(3, 5),
INTERVAL '1 2:3:4.567' DAY(3) TO SECOND(5),
INTERVAL '1 2:3' DAY(3) TO MINUTE,
INTERVAL '1 2' DAY(3) TO HOUR,
INTERVAL '1:2:3.45' HOUR(3) TO SECOND(5),
INTERVAL '1:2' HOUR TO MINUTE,
INTERVAL '9999:2.345' MINUTE(1) TO SECOND(2)
FROM DUAL;
Functions
In addition to creating intervals with literals, Oracle also provides functions. As opposed to the literals – using the functions allows for passing variables or referencing column values. The functions are NUMTODSINTERVAL and TO_DSINTERVAL.
NUMTODSINTERVAL is fairly straight forward. It takes two parameters: a number, and a character string of one of the unit names: ‘DAY’, ‘HOUR’, ‘MINUTE’, or ‘SECOND’. It will return an INTERVAL DAY(9) TO SECOND(9) filled with the appropriate number of units, rolling them across the various fields of the interval as needed to canonicalize them into days, 0-23 hours, 0-59 minutes, and 0-59.999999999 seconds.
SQL> select numtodsinterval(3.8522463,'day') result from dual 2 union all 3 select numtodsinterval(3.8522463,'hour') from dual 4 union all 5 select numtodsinterval(3.8522463,'minute') from dual 6 union all 7 select numtodsinterval(3.8522463,'second') from dual; RESULT +000000003 20:27:14.080320000 +000000000 03:51:08.086680000 +000000000 00:03:51.134778000 +000000000 00:00:03.852246300
TO_DSINTERVAL 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_DSINTERVAL(‘d h:m:s’). As with full literal durations, the hour, minute, and second components must fall within 0-23 for hours, 0-59 for minutes, and 0-59.999999999 for seconds.
SQL> select TO_DSINTERVAL('1 23:45:06.987654321') sql_format from dual;
SQL_FORMAT
+000000001 23:45:06.987654321
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 days, hours, minutes, and/or seconds may be included in order of descending magnitude with D, H, M, and S as suffixes to follow the numeric values. If you only need a day count in your interval then the format is simply PnD or -PnD where n is the positive integer number of days. If time components are to be included they need to be prefixed with T before adding them.
Thus the legal format templates (not counting the optional minus sign) are:
PnDTnHnMnS -- days, hours, minutes, seconds PnDTnHnM -- days, hours, minutes PnDTnHnS -- days, hours, seconds PnDTnH -- days, hours PnDTnMnS -- days, minutes, seconds PnDTnM -- days, minutes PnDTnS -- days, seconds PnD -- days PTnHnMnS -- hours, minutes, seconds PTnHnM -- hours, minutes PTnHnS -- hours seconds PTnH -- hours PTnMnS -- minutes seconds PTnM -- minutes PTnS -- seconds
Again, the “n” stands for a numeric value for the suffixed units of days, hours, minutes, or seconds. 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 hours to be 0-23 or minutes 0-59 or seconds 0-59.999999999. Any positive integer can be used for day, hour, and minute values. Any positive number can be used for the seconds. The function will sum the values and roll the values up to into larger units as needed. Thus TO_DSINTERVAL(‘PT99M’) will return an interval of one hour and thirty-nine minutes.
SQL> select TO_DSINTERVAL('PT99M') from dual;
TO_DSINTERVAL('PT99M')
+000000000 01:39:00.000000000
The ISO format supports multiple such additions. In the following example 10 days, 50 hours, 99 minutes, and 1000.365 seconds are added together to create a single interval of 12 days, 3 hours, 55 minutes, and 40.365 seconds.
SQL> select to_dsinterval('P10DT50H99M1000.365S') result from dual;
RESULT
+000000012 03:55:40.365000000
Timestamp Math
The default type for subtracting a timestamp from another timestamp or a date is INTERVAL DAY TO SECOND. The precision of the days will be maximized at 9 while the seconds will be based on the larger precision of the two values (DATE types have precision 0 since they can’t support sub-second detail.) Thus a TIMESTAMP(4) minus a TIMESTAMP(5) will result in an INTERVAL DAY(9) TO SECOND(5) value for the difference.
The default precision of the differences may be overridden by specifying the desired type after the subtraction-expression. The expression should be in parentheses to tell the parser the type declaration applies to the subtraction-expression and not the subtrahend. In the example below, a TIMESTAMP(5) will be subtracted from a TIMESTAMP(4). First the result will be returned without modification and in the second query the result will be forced to an INTERVAL DAY TO SECOND(3), this will result in rounding of the result to fit in the smaller precision.
SQL> SELECT (ts1 - ts2) diff
2 FROM (SELECT CAST(TIMESTAMP '2020-12-16 19:07:00.5678 US/Eastern' AS TIMESTAMP(4)) ts1,
3 CAST(TIMESTAMP '2020-11-02 14:07:43.44913 US/Eastern' AS TIMESTAMP(5)) ts2
4 FROM DUAL);
DIFF
+000000044 04:59:17.11867
SQL> SELECT (ts1 - ts2) DAY TO SECOND(3) diff
2 FROM (SELECT CAST(TIMESTAMP '2020-12-16 19:07:00.5678 US/Eastern' AS TIMESTAMP(4)) ts1,
3 CAST(TIMESTAMP '2020-11-02 14:07:43.44913 US/Eastern' AS TIMESTAMP(5)) ts2
4 FROM DUAL);
DIFF
+44 04:59:17.119
Note, specifying a SECOND precision also forced a default precision of 2 when not specified on the DAY portion even though it has been 9 previously. In a similar way, specifying a DAY precision without a SECOND precision will change to the default 6. Here is the same subtraction as the previous two; but DAY(3) is specified while allowing the SECONDs to default.
SQL> SELECT (ts1 - ts2) DAY(3) TO SECOND diff
2 FROM (SELECT CAST(TIMESTAMP '2020-12-16 19:07:00.5678 US/Eastern' AS TIMESTAMP(4)) ts1,
3 CAST(TIMESTAMP '2020-11-02 14:07:43.44913 US/Eastern' AS TIMESTAMP(5)) ts2
4 FROM DUAL);
DIFF
+044 04:59:17.118670
An interesting quirk (bug?) arises if you have the same result twice in the result set, but with different precision specified for each. All of them will be forced to the precision of the last column defined in the result set. Here they are DAY(5)/SECOND(3) because it came last. In the second query they are reversed and DAY(4)/SECOND(2) is forced on all results.
SQL> SELECT (ts1 - ts2) DAY(4) TO SECOND(2) diff1, (ts1 - ts2) DAY(5) TO SECOND(3) diff2
2 FROM (SELECT CAST(TIMESTAMP '2020-12-16 19:07:00.5678 US/Eastern' AS TIMESTAMP(4)) ts1,
3 CAST(TIMESTAMP '2020-11-02 14:07:43.44913 US/Eastern' AS TIMESTAMP(5)) ts2
4 FROM DUAL);
DIFF1 DIFF2
+00044 04:59:17.119 +00044 04:59:17.119
SQL> SELECT (ts1 - ts2) DAY(5) TO SECOND(3) diff1, (ts1 - ts2) DAY(4) TO SECOND(2) diff2
2 FROM (SELECT CAST(TIMESTAMP '2020-12-16 19:07:00.5678 US/Eastern' AS TIMESTAMP(4)) ts1,
3 CAST(TIMESTAMP '2020-11-02 14:07:43.44913 US/Eastern' AS TIMESTAMP(5)) ts2
4 FROM DUAL);
DIFF1 DIFF2
+0044 04:59:17.12 +0044 04:59:17.12
The last-defines-all effect even applies for defaults. In the first query below, we can see the DAY(4)/SECOND(3) rule of the last column is applied to both results; but in the second, the default precision of DAY(9) and SECOND inherited from the larger TIMESTAMP precision is enforced on all results.
SQL> SELECT (ts1 - ts2) diff1, (ts1 - ts2) DAY(4) TO SECOND(3) diff2
2 FROM (SELECT CAST(TIMESTAMP '2020-12-16 19:07:00.5678 US/Eastern' AS TIMESTAMP(4)) ts1,
3 CAST(TIMESTAMP '2020-11-02 14:07:43.44913 US/Eastern' AS TIMESTAMP(5)) ts2
4 FROM DUAL);
DIFF1 DIFF2
+0044 04:59:17.119 +0044 04:59:17.119
SQL> SELECT (ts1 - ts2) DAY(4) TO SECOND(3) diff1, (ts1 - ts2) diff2
2 FROM (SELECT CAST(TIMESTAMP '2020-12-16 19:07:00.5678 US/Eastern' AS TIMESTAMP(4)) ts1,
3 CAST(TIMESTAMP '2020-11-02 14:07:43.44913 US/Eastern' AS TIMESTAMP(5)) ts2
4 FROM DUAL);
DIFF1 DIFF2
+000000044 04:59:17.11867 +000000044 04:59:17.11867
And, of course, 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 or INTERVAL’s precision.
SQL> SELECT DATE '2020-12-20' + INTERVAL '1:2:3.45' HOUR TO SECOND FROM DUAL; DATE'2020-12-20'+IN 2020-12-20 01:02:03 SQL> SELECT TIMESTAMP '2020-12-20 00:00:00' + INTERVAL '1:2:3.45' HOUR TO SECOND FROM DUAL; TIMESTAMP'2020-12-2000:00:00'+INTERVAL'1:2:3.45'HOURTOSECOND 2020-12-20 01:02:03.450000000 SQL> SELECT TIMESTAMP '2020-12-20 00:00:00 US/Eastern' + INTERVAL '1:2:3.45' HOUR TO SECOND FROM DUAL; TIMESTAMP'2020-12-2000:00:00US/EASTERN'+INTERVAL'1:2:3.45'HOURTOSECOND 2020-12-20 01:02:03.450000000 US/EASTERN
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 DSINTERVAL_SUM_TYPE
AS OBJECT
(
v_result INTERVAL DAY(9) TO SECOND(9), -- our working result
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT dsinterval_sum_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate(
self IN OUT dsinterval_sum_type,
p_interval IN INTERVAL DAY TO SECOND
)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge(
self IN OUT dsinterval_sum_type,
ctx2 IN dsinterval_sum_type
)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate(
self IN dsinterval_sum_type,
returnvalue OUT INTERVAL DAY TO SECOND,
flags IN NUMBER
)
RETURN NUMBER
);
CREATE OR REPLACE TYPE BODY DSINTERVAL_SUM_TYPE
IS
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT dsinterval_sum_type)
RETURN NUMBER
IS
BEGIN
ctx := dsinterval_sum_type(NULL); -- initialize the sum to NULL
RETURN odciconst.success;
END odciaggregateinitialize;
MEMBER FUNCTION odciaggregateiterate(
self IN OUT dsinterval_sum_type,
p_interval IN INTERVAL DAY TO SECOND
)
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 dsinterval_sum_type,
ctx2 IN dsinterval_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 dsinterval_sum_type,
returnvalue OUT INTERVAL DAY TO SECOND,
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 dsintervalsum(p_interval dsinterval_unconstrained)
RETURN dsinterval_unconstrained
PARALLEL_ENABLE
AGGREGATE USING dsinterval_sum_type;
Once you have the function, you can use it anywhere you’d use a native aggregate.
SQL> select dsintervalsum(result) from 2 ( 3 select numtodsinterval(3.8522463,'day') result from dual 4 union all 5 select numtodsinterval(3.8522463,'hour') from dual 6 union all 7 select numtodsinterval(3.8522463,'minute') from dual 8 union all 9 select numtodsinterval(3.8522463,'second') from dual); DSINTERVALSUM(RESULT) +000000004 00:22:17.154024000
In the next article I will cover the INTERVAL YEAR TO MONTH type. It is simpler in that it only has 2 components: years and months; but the type has its own challenges as well since years and months vary in size, whereas days, hours, minutes, and seconds are all consistent within the INTERVAL DAY TO SECOND type.