INTERVAL qual1 TO qual2

The INTERVAL data type stores spans of time as Year/Month or Day/Hour/Minute/Second/Fraction units.

Syntax 1: year-month class interval

  INTERVAL YEAR[(precision)] TO MONTH
| INTERVAL YEAR[(precision)] TO YEAR
| INTERVAL MONTH[(precision)] TO MONTH

Syntax 2: day-time class interval

  INTERVAL DAY[(precision)] TO FRACTION[(scale)]
| INTERVAL DAY[(precision)] TO SECOND
| INTERVAL DAY[(precision)] TO MINUTE
| INTERVAL DAY[(precision)] TO HOUR
| INTERVAL DAY[(precision)] TO DAY

| INTERVAL HOUR[(precision)] TO FRACTION[(scale)]
| INTERVAL HOUR[(precision)] TO SECOND
| INTERVAL HOUR[(precision)] TO MINUTE
| INTERVAL HOUR[(precision)] TO HOUR

| INTERVAL MINUTE[(precision)] TO FRACTION[(scale)]
| INTERVAL MINUTE[(precision)] TO SECOND
| INTERVAL MINUTE[(precision)] TO MINUTE

| INTERVAL SECOND[(precision)] TO FRACTION[(scale)]
| INTERVAL SECOND[(precision)] TO SECOND

| INTERVAL FRACTION TO FRACTION[(scale)]
  1. precision defines the number of significant digits of the first qualifier, it must be an integer from 1 to 9. For YEAR, the default is 4. For all other time units, the default is 2. For example, YEAR(5) indicates that the INTERVAL can store a number of years with up to 5 digits.
  2. scale defines the scale of the fractional part, it can be 1, 2, 3, 4 or 5.

Usage

The INTERVAL data type stores a span of time, the difference between two points in time. It can also be used to store quantities that are measured in units of time, such as ages or times.

The INTERVAL data type falls into two classes. These are mutually exclusive because year and month are not fixed-length units of time, and therefore incompatible with INTERVAL data types whose time units are smaller than month:

  • Year-Time intervals store a span of years, months or both.
  • Day-Time intervals store a span of days, hours, minutes, seconds and fraction of seconds, or a contiguous subset of those units.

INTERVAL variables are initialized to NULL in functions, modules and globals.

INTERVAL variables can be assigned from interval literals, by using the INTERVAL() q1 TO q2 notation:
DEFINE iv INTERVAL DAY(5) TO SECOND
LET iv = INTERVAL(-7634 14:23:55) DAY(5) TO SECOND
INTERVAL variables can be assigned from string literals, by using the format YYYY-MM or DD hh:mm:ss.fffff, depending on the interval class:
DEFINE iv INTERVAL DAY(5) TO SECOND
LET iv = "-7634 14:23:55"
INTERVAL variables defined with a single time unit can be assigned from integer values, by using the UNITS operator:
DEFINE iv INTERVAL SECOND(5) TO SECOND
LET iv = 567 UNITS SECOND
The INTERVAL type is used for DATETIME computation.

Depending on the data type of the operands, DATETIME or DECIMAL, the arithmetic operations give different resulting types:

Table 1. Arithmetic operands for the INTERVAL, DATETIME, and DECIMAL data types
Left Operand Type Operator Right Operand Type Result Type
INTERVAL * DECIMAL INTERVAL
INTERVAL / DECIMAL INTERVAL
INTERVAL - INTERVAL INTERVAL
INTERVAL + INTERVAL INTERVAL
DATETIME - INTERVAL DATETIME
DATETIME + INTERVAL DATETIME
DATETIME - DATETIME INTERVAL
The next example shows how to use INTERVAL with DATETIME variables:
MAIN
  DEFINE iym1, iym2 INTERVAL YEAR TO MONTH,
         dt1, dt2 DATETIME YEAR TO MINUTE,
         diff INTERVAL DAY(5) TO MINUTE
  LET iym1 = "2342-4"
  LET iym2 = "-55-11"
  DISPLAY iym1 + iym2
  LET dt1 = CURRENT
  LET dt2 = "2010-12-24 00:00"
  LET diff = dt1 - dt2
  DISPLAY diff
  LET diff = INTERVAL(-7634 14:23) DAY(5) TO MINUTE
  DISPLAY diff
END MAIN

For example, in the expression above DISPLAY iym1 + iym2, both values are from the same INTERVAL class, that is both are year-month, and the result of the DATETIME+INTERVAL calculation is a DATETIME value: 

Result: DATETIME 2286-05 YEAR TO MONTH

INTERVAL values can be negative.

In order to check if an INTERVAL is negative, use the UNITS operator, to produce an interval constant for the comparison. Using numeric constants will not work:
MAIN
    DEFINE start, end DATETIME YEAR TO SECOND
    DEFINE diff INTERVAL SECOND(9) TO SECOND
    LET start = CURRENT + 100 UNITS SECOND
    LET end   = CURRENT - 200 UNITS SECOND
    LET diff = end - start
    IF diff < 0 THEN
       DISPLAY "this will not display!"
    END IF
    IF diff < 0 UNITS SECOND THEN
       DISPLAY "negative interval"
    ELSE
       DISPLAY "positive interval"
    END IF
END MAIN

Data type conversion can be controlled by catching the runtime exceptions. For more details, see Handling type conversion errors.