# 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.