SQLearnAcademy
←Function reference
Dates and timesPostgreSQL only

MAKE_INTERVAL

Builds an interval from separate numbers: years, months, days, hours.

Syntax

MAKE_INTERVAL(<unit> => <amount>, …)

Handy when a duration is stored as a number or calculated. The arguments are named, so you give only the ones you need, and the rest are zero. Unlike gluing text together, (n || ' days')::interval, you don't have to think about the format. Units: years, months, weeks, days, hours, mins, secs.

An interval from numbers
SELECT MAKE_INTERVAL(days => 10) AS ten_days,
MAKE_INTERVAL(hours => 1, mins => 30) AS ninety_minutes;
ten_daysninety_minutes
10 days01:30:00
Shifting by a calculated number of days
SELECT n,
TIMESTAMP '2026-09-13' + MAKE_INTERVAL(days => n) AS day
FROM GENERATE_SERIES(1, 3) AS n;
nday
12026-09-14 00:00:00
22026-09-15 00:00:00
32026-09-16 00:00:00
Versus gluing text together
SELECT MAKE_INTERVAL(days => 3) AS make_interval,
(3 || ' days')::interval AS from_text;
make_intervalfrom_text
3 days3 days

Watch out

Without names, the arguments go in order, and the first one is years: MAKE_INTERVAL(10) is 10 years, not days.

Minutes are mins and seconds are secs: MAKE_INTERVAL(minutes => 5) throws function make_interval(minutes => integer) does not exist.

Only seconds can be fractional: MAKE_INTERVAL(days => 1.5) is an error. A day and a half is MAKE_INTERVAL(days => 1, hours => 12).

Related functions

MAKE_DATEAGENOW