The INTERVAL data type holds an interval of time. It has a number of subtypes:
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 table below.
Interval qualifier | Interval string format | Example |
---|---|---|
YEAR | '<year>' or '<sign> <year>' | '10' or '-10' |
YEAR TO MONTH | '<year>-<month>' or '<sign> <year>-<month>' | '2-06' or '- 2-06' |
MONTH | '<month>' or '<sign> <month>' | '18' or '-18' |
DAY | '<day>' or '<sign> <day>' | '30' or '-30' |
DAY TO HOUR | '<day> <hour>' or '<sign> <day> <hour>' | '1 02' or '-1 02' |
DAY TO MINUTE | '<day> <hour>:<minute>' or '<sign> <day> <hour>:<minute>' | '1 02:30' or '-1 02:30' |
DAY TO SECOND | '<day> <hour>:<minute>:<second>' or '<sign> <day> <hour>:<minute>:<second>' | '1 02:30:15' or '-1 02:30:15.333' |
HOUR | '<hour>' or '<sign> <hour>' | '24' or '-24' |
HOUR TO MINUTE | '<hour>:<minute>' or '<sign> <hour>:<minute>' | '1:30' or '-1:30' |
HOUR TO SECOND | '<hour>:<minute>:<second>' or '<sign> <hour>:<minute>:<second>' | '1:29:59' or '-1:29:59.333' |
MINUTE | '<minute>' or '<sign> <minute>' | '90' or '-90' |
MINUTE TO SECOND | '<minute>:<second>' or '<sign> <minute>:<second>' | '89:59' or '-89:59' |
SECOND | '<second>' or '<sign> <second>' | '15' or '-15.7' |
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:
The largest value of the left-most value in an interval is +/- 2147483647.
Some examples of valid interval values are:
Some examples of invalid interval values are:
A day field is specified, so the hours field is constrained to [0,23].
An hour field is specified, so minutes are constrained to [0,59].
Here are some examples of interval literals:
INTERVAL '1' HOUR INTERVAL '90' MINUTE INTERVAL '1-06' YEAR TO MONTH