Literal INTERVAL

The Literal INTERVAL segment specifies a literal INTERVAL value. Use this whenever you see a reference to a literal INTERVAL in a syntax diagram.

Syntax

(1)
Literal INTERVAL

1  INTERVAL ( %Numeric Time
Span )  %INTERVAL Field Qualifier1
Numeric Time Span

1 
2.1!   +  
2.1  -  
2.1  dd?  space hh?  : mi?  : ss?  . fffff
2.1  hh?  : mi?  : ss?  . fffff
2.1  mi?  : ss?  . fffff
2.1  ss?  . fffff
2.1  . fffff
1 
2.1!   +  
2.1  -  
2.1  yyyy?   -   mo
2.1 mo
Notes:

Element Description Restrictions Syntax
dd Number of days -10**10 < dd < 10**10 Literal Number
fffff Fractions of a second 0 < fffff < 9999 Literal Number
hh Number of hours If not first, 0 < hh < 23 Literal Number
mi Number of minutes If not first, 0 < mi < 59 Literal Number
mo Number of months If not first, 0 < mo < 11 Literal Number
space Blank space (ASCII 32) Exactly 1 blank character is required Literal blank space
ss Number of seconds If not first, 0 < ss < 59 Literal Number
yyyy Number of years -10**10 < yyyy < 10**10 Literal Number

Usage

Unlike DATETIME literals, INTERVAL literals can include the unary plus ( + ) or unary minus ( - ) sign. If you specify no sign, the default is plus.

The precision of the first time unit can be specified by the INTERVAL qualifier. Except for FRACTION, which can have no more than 5 digits of precision, the first time unit can have up to 9 digits of precision, if you specified a nondefault precision in the declaration of the INTERVAL column or variable.

The following examples show literal INTERVAL values: 

INTERVAL (3-6) YEAR TO MONTH
INTERVAL (09:55:30.825) HOUR TO FRACTION
INTERVAL (40 5) DAY TO HOUR
INTERVAL (299995.2567) SECOND(6) TO FRACTION(4)

Only the last of these examples has nondefault precision. For the syntax of declaring the precision of INTERVAL data types and the default values for each time unit, refer to INTERVAL Field Qualifier.