Language basics / Data types |
The INTERVAL data type stores spans of time as Year/Month or Day/Hour/Minute/Second/Fraction units.
INTERVAL YEAR[(precision)] TO MONTH | INTERVAL YEAR[(precision)] TO YEAR | INTERVAL MONTH[(precision)] TO MONTH
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)]
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.
DEFINE iv INTERVAL DAY(5) TO SECOND LET iv = INTERVAL(-7634 14:23:55) DAY(5) TO SECOND
DEFINE iv INTERVAL DAY(5) TO SECOND LET iv = "-7634 14:23:55"
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:
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 |
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.