Search Shortcut cmd + k | ctrl + k
Search cmd+k ctrl+k
0.10 (stable)
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
+ 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) Get subfield (equivalent to extract).
datepart(part, interval) Alias of date_part. Get subfield (equivalent to extract).
extract(part FROM interval) Get subfield from an interval.
epoch(interval) Get total number of seconds 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 millenium 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 parts are defined for intervals.

date_part(part, interval)

Description Get subfield (equivalent to extract).
Example date_part('year', INTERVAL '14 months')
Result 1

datepart(part, interval)

Description Alias of date_part. Get subfield (equivalent to extract).
Example datepart('year', INTERVAL '14 months')
Result 1

extract(part FROM interval)

Description Get subfield from an interval.
Example extract('month' FROM INTERVAL '14 months')
Result 2

epoch(interval)

Description Get total number of seconds 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 millenium 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
About this page

Last modified: 2024-03-29