INTERVAL Field Qualifier

The INTERVAL field qualifier specifies the precision, in time units, for an INTERVAL value. Use the INTERVAL Field Qualifier segment whenever you see a reference to an INTERVAL field qualifier in a syntax diagram.

Syntax

INTERVAL Field Qualifier

2.1  DAY? (precision)
2.2.1 TO DAY
2.2.1 TO HOUR
2.2.1 TO MINUTE
2.2.1 TO SECOND
2.2.1  TO FRACTION
2.2.2.1 ( scale )
2.1  HOUR? (precision)
2.2.1 TO HOUR
2.2.1 TO MINUTE
2.2.1 TO SECOND
2.2.1  TO FRACTION
2.2.2.1 ( scale )
2.1  MINUTE? (precision)
2.2.1 TO MINUTE
2.2.1 TO SECOND
2.2.1  TO FRACTION
2.2.2.1 ( scale )
2.1  SECOND? (precision)
2.2.1 TO SECOND
2.2.1  TO FRACTION
2.1  FRACTION  TO FRACTION
2.2.1 ( scale )
2.1  YEAR? (precision)
2.2.1 TO YEAR
2.2.1 TO MONTH
2.1  MONTH? (precision) TO MONTH

Element Description Restrictions Syntax
scale Integer number of digits in FRACTION field. Default is 3. Must be in the range from 1 to 5 Literal Number
precision Integer number of digits in the largest time unit that the INTERVAL includes. For YEAR, the default is 4. For all other time units except FRACTION, the default is 2. Must be in the range from 1 to 9 Literal Number

Usage

This segment specifies the precision and scale of an INTERVAL data type.

A keyword specifying the largest time unit must be the first keyword, and a keyword specifying the smallest time unit must follow the TO keyword. These can be the same keyword. This segment resembles the syntax of a DATETIME Field Qualifier, but with these exceptions:
  • If the largest time unit keyword is YEAR or MONTH, the smallest time unit keyword cannot specify a time unit smaller than MONTH.
  • You can specify up to 9-digit precision after the first time unit, unless FRACTION is the first time unit (in which case no precision is valid after the first FRACTION keyword, but you can specify up to 5 digits of scale after the second FRACTION keyword).

Because year and month are not fixed-length units of time, the database server treats INTERVAL data types that include the YEAR or MONTH keywords in their qualifiers as incompatible with INTERVAL data types whose qualifiers are time units smaller than MONTH. The database server supports no implicit casts between these two categories of INTERVAL data types.

The next two examples show YEAR TO MONTH qualifiers of INTERVAL data types. The first example can hold an interval of up to 999 years and 11 months, because it gives 3 as the precision of the YEAR field. The second example uses the default precision on the YEAR field, so it can hold an interval of up to 9,999 years and 11 months. 
YEAR (3) TO MONTH

YEAR TO MONTH

When you want a value to specify only one kind of time unit, the first and last qualifiers are the same. For example, an interval of whole years is qualified as YEAR TO YEAR or YEAR (5) TO YEAR, for an interval of up to 99,999 years.

The following examples show several forms of INTERVAL field qualifiers: 
YEAR(5) TO MONTH

DAY (5) TO FRACTION(2)

DAY TO DAY

FRACTION TO FRACTION (4)

For information about how to specify INTERVAL field qualifiers and how to use INTERVAL data in arithmetic and relational operations, see the related reference, INTERVAL data type.