ESQL INTERVAL data type
The INTERVAL
data type holds an interval
of time.
This data type has a number of subtypes:
- YEAR
- YEAR TO MONTH
- MONTH
- DAY
- DAY TO HOUR
- DAY TO MINUTE
- DAY TO SECOND
- HOUR
- HOUR TO MINUTE
- HOUR TO SECOND
- MINUTE
- MINUTE TO SECOND
- SECOND
All these subtypes describe intervals of time and all can take part in the full range of operations of the INTERVAL type; for example, addition and subtraction operations with values of type DATE, TIME, or TIMESTAMP.
Use the CAST function to convert from one subtype to another, except for intervals described in years and months, or months, which cannot be converted to those described in days, hours, minutes, and seconds.
The split between months and days arises because the number of days in each month varies. An interval of one month and a day is not meaningful, and cannot be sensibly converted into an equivalent interval in numbers of days only.
An interval literal is defined by the syntax:
INTERVAL <interval string> <interval qualifier>
The format of interval string and interval qualifier are defined by the following table.
Interval qualifier | Interval string format | Examples |
---|---|---|
YEAR | [sign]'[sign]year' | INTERVAL '10' YEAR (next 10 years)
|
YEAR TO MONTH | [sign]'[sign]year-month' | INTERVAL '2-06' YEAR TO MONTH |
MONTH | [sign]'[sign]month' | INTERVAL '18' MONTH |
DAY | [sign]'[sign]day' | INTERVAL '30' DAY |
DAY TO HOUR | [sign]'[sign]day hour' | INTERVAL '1 02' DAY TO HOUR |
DAY TO MINUTE | [sign]'[sign]day hour:minute' | INTERVAL '1 02:30' DAY TO MINUTE |
DAY TO SECOND | [sign]'[sign]day hour:minute:second' | INTERVAL '1 02:30:15' DAY TO SECOND |
HOUR | [sign]'[sign]hour' | INTERVAL '24' HOUR |
HOUR TO MINUTE | [sign]'[sign]hour:minute' | INTERVAL '1:30' HOUR TO MINUTE |
HOUR TO SECOND | [sign]'[sign]hour:minute:second' | INTERVAL '1:29:59' HOUR TO SECOND |
MINUTE | [sign]'[sign]minute' | INTERVAL '90' MINUTE |
MINUTE TO SECOND | [sign]'[sign]minute:second' | INTERVAL '89:59' MINUTE TO SECOND |
SECOND | [sign]'[sign]second' | INTERVAL '15' SECOND |
Where an interval contains both a year and a month value,
a hyphen is used between the two values. In this instance, the month
value must be within the range [0,11]
. If an interval
contains a month value and no year value, the month value is unconstrained.
A space is used to separate days from the rest of the interval.
If an interval contains more than one of HOUR, MINUTE, and SECOND, a colon is needed to separate the values and all except the leftmost are constrained as follows:
- HOUR
- 0-23
- MINUTE
- 0-59
- SECOND
- 0-59.999...
The largest value of the leftmost value in an interval is +/- 2147483647.
Some examples of valid interval values are:
- 72 hours
- 3 days: 23 hours
- 3600 seconds
- 90 minutes: 5 seconds
Some examples of invalid interval values are:
- 3 days: 36 hours
A day field is specified, so the hours field is constrained to
[0,23]
. - 1 hour: 90 minutes
An hour field is specified, so minutes are constrained to
[0,59]
.
The following code is an example of how to define INTERVAL literals in your code:
DECLARE i1,i2,i3,i4,i5 INTERVAL;
SET i1 = INTERVAL '10' YEAR;
SET i2 = INTERVAL '-10' YEAR;
SET i3 = INTERVAL -'10' YEAR;
SET i4 = INTERVAL -'-10' YEAR;
SET i5 = INTERVAL '-2-06' YEAR TO MONTH;