Interval Functions

Search Shortcut cmd + k | ctrl + k

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`

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.

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`

Description	Alias of `date_part`.
Example	`datepart('year', INTERVAL '14 months')`
Result	`1`

Description	Alias of `date_part`.
Example	`extract('month' FROM INTERVAL '14 months')`
Result	2

Description	Get total number of seconds, as double precision floating point number, in interval.
Example	`epoch(INTERVAL 5 HOUR)`
Result	`18000.0`

Description	Construct a century interval.
Example	`to_centuries(5)`
Result	`INTERVAL 500 YEAR`

Description	Construct a microsecond interval.
Example	`to_microseconds(5)`
Result	`INTERVAL 5 MICROSECOND`

Description	Construct a millennium interval.
Example	`to_millennia(5)`
Result	`INTERVAL 5000 YEAR`

Description	Construct a millisecond interval.
Example	`to_milliseconds(5)`
Result	`INTERVAL 5 MILLISECOND`

Description	Construct an interval of `integer` quarters.
Example	`to_quarters(5)`
Result	`INTERVAL 1 YEAR 3 MONTHS`