MAKE_INTERVAL

Construct an INTERVAL value from optional year, month, week, day, hour, minute, and second components.

Category: datetimeReturns: INTERVALDialect: PostgreSql

Syntax

MAKE_INTERVAL([years] [, months] [, weeks] [, days] [, hours] [, mins] [, secs])

Description

## Overview Constructs an INTERVAL value from optional component fields. All parameters default to 0, so any subset can be supplied as keyword-style arguments. Weeks are stored as 7 days internally; months and years are stored symbolically (calendar-aware when added to a timestamp). Use MAKE_INTERVAL when component counts are driven by column values or when the natural interval-literal syntax (INTERVAL '1 year 6 months') is inconvenient. ## Behavior - Returns an INTERVAL. - Returns NULL if any provided argument is NULL. - All components default to 0; MAKE_INTERVAL() returns a zero interval. - Negative values are allowed and produce a negative interval. - Weeks are converted to days (1 week = 7 days) at construction time. - Months and years are stored symbolically: adding an interval of '1 month' to a date is calendar-aware (it respects actual month length). - Seconds accept fractional values. ## Compatibility - Matches the MAKE_INTERVAL convention with the standard component order (years, months, weeks, days, hours, minutes, seconds).

Parameters

Name	Type	Description
`years`		Specifies the number of years. Defaults to 0 when omitted.
`months`		Specifies the number of months. Defaults to 0 when omitted.
`weeks`		Specifies the number of weeks, stored internally as 7 days each. Defaults to 0 when omitted.
`days`		Specifies the number of days. Defaults to 0 when omitted.
`hours`		Specifies the number of hours. Defaults to 0 when omitted.
`mins`		Specifies the number of minutes. Defaults to 0 when omitted.
`secs`		Specifies the number of seconds including fractional seconds. Defaults to 0 when omitted.

Examples

-- Interval of 1 year 6 months
SELECT MAKE_INTERVAL(1, 6) AS interval;

-- 2 days 12 hours
SELECT MAKE_INTERVAL(0, 0, 0, 2, 12) AS interval;

-- Fractional seconds
SELECT MAKE_INTERVAL(0, 0, 0, 0, 0, 0, 90.5) AS interval;

-- Add a constructed interval to a timestamp
SELECT TIMESTAMP '2025-01-01 00:00:00' + MAKE_INTERVAL(0, 3, 0, 15) AS future_ts;

-- Build retry backoff intervals from a column
SELECT retry_id, MAKE_INTERVAL(0, 0, 0, 0, 0, 0, 2.0 * attempt_number) AS backoff
FROM ops.scheduler.retries;

-- Combine weeks and days: 2 weeks 3 days = 17 days
SELECT MAKE_INTERVAL(0, 0, 2, 3) AS combined;

Pitfalls

Months and years are calendar-aware when added to a TIMESTAMP: '2025-01-31' + MAKE_INTERVAL(0, 1) yields 2025-02-28 (end-of-month clamping), not 2025-03-03.
Weeks are immediately folded into days; the resulting interval does not retain a separate 'weeks' component. Round-trip extraction must sum days and divide by 7.
Adding an interval across a DST transition using local-time arithmetic can produce a different wall-clock time than expected. Prefer UTC arithmetic when DST-invariance matters.
Positional arguments: MAKE_INTERVAL(0, 0, 0, 2, 12) is 'zero years, zero months, zero weeks, 2 days, 12 hours'. Miscounting leading zeros is a common error; named-argument syntax (where supported) avoids this.