INTERVAL qualifier

You are here:

INTERVAL Qualifier

INTERVAL qualifier contains information about the largest unit of time used in the INTERVAL value and about the smallest one.

The first time unit (before the TO keyword) must always be larger than the last one (after the TO keyword).

INTERVAL qualifier for the year-month INTERVAL type has the following structure:

YEAR	Specifies that years are used as the largest or the smallest time unit used to measure the elapsed time
MONTH	Specifies that months are used as the largest or the smallest time unit used to measure the elapsed time
Size	An integer from 1 to 9 that specifies the precision of the first INTERVAL qualifier

DEFINE inter1 INTERVAL YEAR TO YEAR

INTERVAL qualifier for the day-time INTERVAL type has the following structure:

DAY	Specifies that days are used as the largest or the smallest time unit used to measure the elapsed time
HOUR	Specifies that hours are used as the largest or the smallest time unit used to measure the elapsed time
MINUTE	Specifies that minutes are used as the largest or the smallest time unit used to measure the elapsed time
SECOND	Specifies that seconds are used as the largest or the smallest time unit used to measure the elapsed time
FRACTION	Specifies that fractions of a second are used as the largest or the smallest time unit used to measure the elapsed time
Size	An integer from 1 to 9 (from 1 to 5 for FRACTION qualifier) that specifies the precision of an INTERVAL qualifier

DEFINE inter2 INTERVAL HOUR TO FRACTION(3)

It will cause a compile-time error, if you try to mix qualifiers of year-month and day-time types of interval. The qualifier MONTH TO DAY and other mixing qualifiers are not valid.

Time unit in the last INTERVAL qualifier may be of the same as the time unit in the first one, but it cannot be a larger time unit. Thus HOUR TO DAY and MONTH TO YEAR are not valid qualifiers.

To specify only one unit of time as a qualifier, you need to specify it as the first and the last keyword: YEAR TO YEAR. It will cause a compile-time error if you try to specify the time unit only once and omit the keyword TO.

Arithmetic expressions that use year-month INTERVAL values together with DATE values or DATETIME values that do not contain time units smaller that MONTH can return invalid results (like February 30, or June 31). E.g.:

DEFINE

int1 INTERVAL MONTH(4) TO MONTH,

int2 INTERVAL FRACTION TO FRACTION(4),

int3 INTERVAL DAY(5) TO FRACTION(2)

An INTERVAL value can be specified as an INTERVAL literal or as an INTEGER character string.