![]() Previous |
![]() Next |
DSINTERVAL corresponds to the SQL INTERVAL DAY TO SECOND
data type. It stores a period of time in terms of days, hours, minutes, and seconds. This data type is useful for representing the precise difference between two datetime values.
Specify DSINTERVAL
interval literals using the following syntax.
INTERVAL 'integer|integer time_expr|time_expr
DAY|HOUR|MINUTE [(leading_precision)] | SECOND [leading_precision[, fractional_seconds_precision ])]
[ TO DAY|HOUR|MINUTE|SECOND [(fractional_seconds_precision) ]]
where
integer
specifies the number of days. If this value contains more digits than the number specified by the leading precision, then Oracle returns an error.
time_expr
specifies a time in the format HH[:MI[:SS[.n]]]
or MI[:SS[.n]]
or SS[.n]
, where n
specifies the fractional part of a second. If n
contains more digits than the number specified by fractional_seconds_precision
, then n
is rounded to the number of digits specified by the fractional_seconds_precision
value. You can specify time_expr
following an integer and a space only if the leading field is DAY
.
leading_precision
is the number of digits in the leading field. Accepted values are 0 to 9. The default is 2.
fractional_seconds_precision
is the number of digits in the fractional part of the SECOND
datetime field. Accepted values are 1 to 9. The default is 6.
If you specify a trailing field, it must be less significant than the leading field. For example, INTERVAL
MINUTE
TO
DAY
is not valid. Because of this restriction, if SECOND
is the leading field, the interval literal cannot have any trailing field.
The valid range of values for the trailing field are as follows:
HOUR
: 0 to 23
MINUTE
: 0 to 59
SECOND
: 0 to 59.999999999
Examples of the various forms of DSINTERVAL
literals follow, including some abbreviated versions:
Form of Interval Literal | Interpretation |
---|---|
INTERVAL '4 5:12:10.222' DAY TO SECOND(3) |
4 days, 5 hours, 12 minutes, 10 seconds, and 222 thousandths of a second. |
INTERVAL '4 5:12' DAY TO MINUTE |
4 days, 5 hours and 12 minutes. |
INTERVAL '400 5' DAY(3) TO HOUR |
400 days 5 hours. |
INTERVAL '400' DAY(3) |
400 days. |
INTERVAL '11:12:10.2222222' HOUR TO SECOND(7) |
11 hours, 12 minutes, and 10.2222222 seconds. |
INTERVAL '11:20' HOUR TO MINUTE |
11 hours and 20 minutes. |
INTERVAL '10' HOUR |
10 hours. |
INTERVAL '10:22' MINUTE TO SECOND |
10 minutes 22 seconds. |
INTERVAL '10' MINUTE |
10 minutes. |
INTERVAL '4' DAY |
4 days. |
INTERVAL '25' HOUR |
25 hours. |
INTERVAL '40' MINUTE |
40 minutes. |
INTERVAL '120' HOUR(3) |
120 hours. |
INTERVAL '30.12345' SECOND(2,4) |
30.1235 seconds. The fractional second '12345' is rounded to '1235' because the precision is 4. |
You can add or subtract one DAY
TO
SECOND
interval literal from another DAY
TO
SECOND
literal. For example.
INTERVAL'20' DAY - INTERVAL'240' HOUR = INTERVAL'10-0' DAY TO SECOND