Interval Functions
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 00:00:00 |
+ | 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 an 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_quarters(integer) | Construct an interval of integer quarters. |
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 an 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_quarters(integer)
| Description | Construct an interval of integer quarters. |
| Example | to_quarters(5) |
| Result | INTERVAL 1 YEAR 3 MONTHS |
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 |