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.

SQL

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

 years
-------
     1

datepart(part, interval)

Alias of date_part.

SQL

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

 years
-------
     1

extract(part FROM interval)

Alias of date_part.

SQL

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

 months
--------
      2

epoch(interval)

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

SQL

SELECT epoch(INTERVAL 5 HOUR) AS seconds;

 seconds
---------
   18000

to_centuries(integer)

Construct a century interval.

SQL

SELECT to_centuries(5) AS result;

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

to_days(integer)

Construct a day interval.

SQL

SELECT to_days(5) AS result;

 result
--------
 5 days

to_decades(integer)

Construct a decade interval.

SQL

SELECT to_decades(5) AS result;

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

to_hours(integer)

Construct an hour interval.

SQL

SELECT to_hours(5) AS result;

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

to_microseconds(integer)

Construct a microsecond interval.

SQL

SELECT to_microseconds(5) AS result;

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

to_millennia(integer)

Construct a millennium interval.

SQL

SELECT to_millennia(5) AS result;

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

to_milliseconds(integer)

Construct a millisecond interval.

SQL

SELECT to_milliseconds(5) AS result;

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

to_minutes(integer)

Construct a minute interval.

SQL

SELECT to_minutes(5) AS result;

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

to_months(integer)

Construct a month interval.

SQL

SELECT to_months(5) AS result;

 result
--------
 5 mons

to_quarters(integer)

Construct an interval of integer quarters.

SQL

SELECT to_quarters(5) AS result;

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

to_seconds(integer)

Construct a second interval.

SQL

SELECT to_seconds(5) AS result;

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

to_weeks(integer)

Construct a week interval.

SQL

SELECT to_weeks(5) AS result;

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

to_years(integer)

Construct a year interval.

SQL

SELECT to_years(5) AS result;

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