Previous
Previous
 
Next
Next

DSINTERVAL Data Type

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

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:

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