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 in two classes, which are mutually exclusive:

INTERVAL values can be negative.

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, according to 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

Intervals are typically used for DATETIME computation. According to the arithmetic operator, DATETIME or DECIMAL operands are involved:

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

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

Related concepts
DATE
DATETIME qual1 TO qual2