This section describes functions and operators for examining and manipulating INTERVAL values.
Interval Operators
The table below shows the available mathematical operators for INTERVAL types.
| Operator | Description | Example | Result |
|---|---|---|---|
+ |
addition of an INTERVAL |
INTERVAL 1 HOUR + INTERVAL 5 HOUR |
INTERVAL 6 HOUR |
+ |
addition to a DATE |
DATE '1992-03-22' + INTERVAL 5 DAY |
1992-03-27 |
+ |
addition to a TIMESTAMP |
TIMESTAMP '1992-03-22 01:02:03' + INTERVAL 5 DAY |
1992-03-27 01:02:03 |
+ |
addition to a TIME |
TIME '01:02:03' + INTERVAL 5 HOUR |
06:02:03 |
- |
subtraction of an INTERVAL |
INTERVAL 5 HOUR - INTERVAL 1 HOUR |
INTERVAL 4 HOUR |
- |
subtraction from a DATE |
DATE '1992-03-27' - INTERVAL 5 DAY |
1992-03-22 |
- |
subtraction from a TIMESTAMP |
TIMESTAMP '1992-03-27 01:02:03' - INTERVAL 5 DAY |
1992-03-22 01:02:03 |
- |
subtraction from a TIME |
TIME '06:02:03' - INTERVAL 5 HOUR |
01:02:03 |
Interval Functions
The table below shows the available scalar functions for INTERVAL types.
| Name | Description |
|---|---|
date_part(part, interval) |
Extract datepart component (equivalent to extract). See INTERVAL for the sometimes surprising rules governing this extraction. |
datepart(part, interval) |
Alias of date_part. |
extract(part FROM interval) |
Alias of date_part. |
epoch(interval) |
Get total number of seconds, as double precision floating point number, in interval. |
to_centuries(integer) |
Construct a century interval. |
to_days(integer) |
Construct a day interval. |
to_decades(integer) |
Construct a decade interval. |
to_hours(integer) |
Construct a hour interval. |
to_microseconds(integer) |
Construct a microsecond interval. |
to_millennia(integer) |
Construct a millennium interval. |
to_milliseconds(integer) |
Construct a millisecond interval. |
to_minutes(integer) |
Construct a minute interval. |
to_months(integer) |
Construct a month interval. |
to_seconds(integer) |
Construct a second interval. |
to_weeks(integer) |
Construct a week interval. |
to_years(integer) |
Construct a year interval. |
Only the documented date part components are defined for intervals.
date_part(part, interval)
| Description | Extract datepart component (equivalent to extract). See INTERVAL for the sometimes surprising rules governing this extraction. |
| Example | date_part('year', INTERVAL '14 months') |
| Result | 1 |
datepart(part, interval)
| Description | Alias of date_part. |
| Example | datepart('year', INTERVAL '14 months') |
| Result | 1 |
extract(part FROM interval)
| Description | Alias of date_part. |
| Example | extract('month' FROM INTERVAL '14 months') |
| Result | 2 |
epoch(interval)
| Description | Get total number of seconds, as double precision floating point number, in interval. |
| Example | epoch(INTERVAL 5 HOUR) |
| Result | 18000.0 |
to_centuries(integer)
| Description | Construct a century interval. |
| Example | to_centuries(5) |
| Result | INTERVAL 500 YEAR |
to_days(integer)
| Description | Construct a day interval. |
| Example | to_days(5) |
| Result | INTERVAL 5 DAY |
to_decades(integer)
| Description | Construct a decade interval. |
| Example | to_decades(5) |
| Result | INTERVAL 50 YEAR |
to_hours(integer)
| Description | Construct a hour interval. |
| Example | to_hours(5) |
| Result | INTERVAL 5 HOUR |
to_microseconds(integer)
| Description | Construct a microsecond interval. |
| Example | to_microseconds(5) |
| Result | INTERVAL 5 MICROSECOND |
to_millennia(integer)
| Description | Construct a millennium interval. |
| Example | to_millennia(5) |
| Result | INTERVAL 5000 YEAR |
to_milliseconds(integer)
| Description | Construct a millisecond interval. |
| Example | to_milliseconds(5) |
| Result | INTERVAL 5 MILLISECOND |
to_minutes(integer)
| Description | Construct a minute interval. |
| Example | to_minutes(5) |
| Result | INTERVAL 5 MINUTE |
to_months(integer)
| Description | Construct a month interval. |
| Example | to_months(5) |
| Result | INTERVAL 5 MONTH |
to_seconds(integer)
| Description | Construct a second interval. |
| Example | to_seconds(5) |
| Result | INTERVAL 5 SECOND |
to_weeks(integer)
| Description | Construct a week interval. |
| Example | to_weeks(5) |
| Result | INTERVAL 35 DAY |
to_years(integer)
| Description | Construct a year interval. |
| Example | to_years(5) |
| Result | INTERVAL 5 YEAR |