INTERVAL literal

You are here:

INTERVAL Literal

INTERVAL literal of INTERVAL data type consists of the INTERVAL keyword, numeric interval value enclosed in parentheses and INTERVAL qualifier:

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

YYYY	The numeric indication of years
MM	The numeric indication of months
- (Hyphen)	A delimiter used between years and months
INTERVAL qualifier	The qualifier used for declaration of the year-month interval type

For example, an INTERVAL with the INTERVAL qualifier YEAR(2) TO MONTH can have the following realization:

LET inter5 = INTERVAL (87-03) YEAR(2) TO MONTH

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

DD	The numeric indication of days
Space	A space is required between day and hour values
hh	The numeric indication of hours
mm	The numeric indication of minutes
ss	The numeric indication of seconds
: (Colon)	A delimiter used between hours and minutes and between minutes and seconds
fff	A numeric indication of the fractions of a second
. (Dot)	A delimiter which separates fractions from seconds
INTERVAL qualifier	The qualifier used for declaration of the day-time interval type

Like with the qualifier, all the time units of a numeric interval are optional, you omit some time units. However you cannot use them in random order or miss the successive time units between the largest and the smallest time unit you use.

If you specify the size for the first INTERVAL qualifier, it defines the number of digits which can be stored in the first time unit. An interval declared with the DAY (5) TO MINUTE precision can store up to 999 days.

An INTERVAL qualifier defined as DAY(6) TO HOUR can have the following realization:

LET inter3 = INTERVAL(102310 21:46) DAY(6) TO HOUR

All the time units except for the FRACTION can have up to 9 digits of precision (FRACTION can have up to 5 digits). When you specify an INTERVAL literal you may explicitly define the number of significant digits you are entering. If you assign a non-default size and do not specify the precision explicitly, a compile-time error will occur. E.g. If you want to enter 103,25 days for the variable declared as INTERVAL DAY TO HOUR, you must enter:

LET inter4 = INTERVAL(103 06) DAY(3) TO HOUR

It is not necessary to specify every time unit from the declared data type, you can specify only units you need. E.g. if you have declared an INTERVAL variable with DAY TO MINUTE precision, any values can be assigned to it provided that they do not exceed the declared precision. This means you can assign a DAY TO HOUR, DAY TO DAY, HOUR TO MINUTE, HOUR TO HOUR, or MINUTE TO MINUTE values to it, but values that include SECOND and FRACTION will not be valid for a variable declared with DAY TO MINUTE precision. You need to specify only those time units in the numeric value which correspond to the qualifier you use within the INTERVAL literal. You cannot use them in random order or miss the successive time units between the largest and the smallest time unit you use.

INTERVAL(45 20:15:00.234) DAY TO FRACTION -- the correct INTERVAL literal

INTERVAL(45 00.234) DAY TO FRACTION -- the incorrect INTERVAL literal

If you specify the precision of the first time unit, you need to use the number of digits specified. You cannot specify DAY(5), but include 4 or less digits in the day value. If you still need to specify a value which is less than the precision, place zeros before the value to match the precision. E.g. : INTERVAL(00045 05:23) DAY(5) TO MINUTE.