INTERVAL
SQL construct
Defines a span of time, an interval that can be added to dates and subtracted from them.
PostgreSQL 17.5
INTERVAL 'quantity unit[ quantity unit ...]'Parameters
- quantitynumeric
- Number of units, can be fractional or negative
- unitkeyword
- Time unit, plural forms are allowed (days, hours); several pairs are separated by spaces
- Values:microsecondmillisecondsecondminutehourdayweekmonthyeardecadecenturymillennium
Return value
A value of the interval type
Examples
Intervals of different length
PostgreSQL 17.5
SELECT INTERVAL '1 year 2 months 3 days' AS long_interval,
INTERVAL '90 minutes' AS minutes;Date of coming of age
PostgreSQL 17.5
SELECT member_name,
birthday,
birthday + INTERVAL '18 years' AS adult_since
FROM FamilyMembers;Date arithmetic
PostgreSQL 17.5
SELECT DATE '2024-01-31' + INTERVAL '1 month' AS month_later,
TIMESTAMP '2024-03-08 10:00' - TIMESTAMP '2024-03-01 08:30' AS difference,
INTERVAL '1 day' * 3 AS three_days;Payments in the last three months
PostgreSQL 17.5
SELECT payment_id,
date
FROM Payments
WHERE date >= TIMESTAMP '2006-03-12' - INTERVAL '3 months'
ORDER BY date;Details
An interval can be added to a date or subtracted from it and multiplied by a number, and the difference of two timestamp values is also an interval. A date plus an interval gives a timestamp, while the difference of two date values is a whole number of days.
Months take the month length into account: DATE '2024-01-31' + INTERVAL '1 month' gives 2024-02-29. Hours are not turned into days automatically: INTERVAL '36 hours' stays 36:00:00, and JUSTIFY_HOURS turns it into 1 day 12:00:00.