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.

Tip

Only the documented date part components are defined for intervals.

date_part(part, interval)

Extract datepart component (equivalent to extract). See INTERVAL for the sometimes surprising rules governing this extraction.

Query

SELECT date_part('year', INTERVAL '14 months') AS years;

Result

 years-------     1

datepart(part, interval)

Alias of date_part.

Query

SELECT datepart('year', INTERVAL '14 months') AS years;

Result

 years-------     1

extract(part FROM interval)

Alias of date_part.

Query

SELECT extract('month' FROM INTERVAL '14 months') AS months;

Result

 months--------      2

epoch(interval)

Get total number of seconds, as double precision floating point number, in interval.

Query

SELECT epoch(INTERVAL 5 HOUR) AS seconds;

Result

 seconds---------   18000

to_centuries(integer)

Construct a century interval.

Query

SELECT to_centuries(5) AS result;

Result

 result----------- 500 years

to_days(integer)

Construct a day interval.

Query

SELECT to_days(5) AS result;

Result

 result-------- 5 days

to_decades(integer)

Construct a decade interval.

Query

SELECT to_decades(5) AS result;

Result

 result---------- 50 years

to_hours(integer)

Construct an hour interval.

Query

SELECT to_hours(5) AS result;

Result

 result---------- 05:00:00

to_microseconds(integer)

Construct a microsecond interval.

Query

SELECT to_microseconds(5) AS result;

Result

 result----------------- 00:00:00.000005

to_millennia(integer)

Construct a millennium interval.

Query

SELECT to_millennia(5) AS result;

Result

 result------------ 5000 years

to_milliseconds(integer)

Construct a millisecond interval.

Query

SELECT to_milliseconds(5) AS result;

Result

 result-------------- 00:00:00.005

to_minutes(integer)

Construct a minute interval.

Query

SELECT to_minutes(5) AS result;

Result

 result---------- 00:05:00

to_months(integer)

Construct a month interval.

Query

SELECT to_months(5) AS result;

Result

 result-------- 5 mons

to_quarters(integer)

Construct an interval of integer quarters.

Query

SELECT to_quarters(5) AS result;

Result

 result--------------- 1 year 3 mons

to_seconds(integer)

Construct a second interval.

Query

SELECT to_seconds(5) AS result;

Result

 result---------- 00:00:05

to_weeks(integer)

Construct a week interval.

Query

SELECT to_weeks(5) AS result;

Result

 result--------- 35 days

to_years(integer)

Construct a year interval.

Query

SELECT to_years(5) AS result;

Result

 result--------- 5 years