# 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`)__]__

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

`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:

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` |

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

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