INTERVAL
Measures the difference between two points in time. Intervals can be positive or negative. The INTERVAL data type is SQL:2008 compliant, and supports interval qualifiers that are divided into two major subtypes:
- Year-month: Span of years and months
- Day-time: Span of days, hours, minutes, seconds, and fractional seconds
Intervals are represented internally as some number of microseconds and printed as up to 60 seconds, 60 minutes, 24 hours, 30 days, 12 months, and as many years as necessary. You can control the output format of interval units with SET INTERVALSTYLE and SET DATESTYLE.
Syntax
INTERVAL 'interval-literal' [ interval-qualifier ] [ (p) ]
Parameters
| interval-literal |
A character string that expresses an interval, conforming to this format: [-] { quantity subtype-unit }[…] [ AGO ]
For details, see Interval Literal. |
| interval-qualifier | Optionally specifies how to interpret and format an interval literal for output, and, optionally, sets precision. If omitted, the default is DAY TO SECOND(6). For details, see Interval Qualifier. |
|
p |
Specifies precision of the seconds field, where p is an integer between 0 - 6. For details, see Specifying Interval Precision. Default: 6 |
Limits
| Name | Low Value | High Value | Resolution |
|---|---|---|---|
| INTERVAL DAY TO SECOND [(p)] | -106751991 days 04:00:54.775807 | +/-106751991 days 04:00:54.775807 | 1 microsecond |
| INTERVAL YEAR TO MONTH | ~/ -768e15 yrs | ~ 768e15 yrs | 1 month |