make_timestamp and make_interval in SQL: Build Dates and Intervals from Parts

When a date arrives as separate numbers — a year, month and day from a form or report columns — it is tempting to glue a string together and cast it to timestamp. PostgreSQL offers an honest alternative: make_timestamp and make_interval build the value straight from numbers, with no format strings and no locale guesswork.

A timestamp from numeric parts

make_timestamp takes year, month, day, hour, minute and seconds as plain numbers and returns a timestamp. No string parsing means no surprises about MM/DD versus DD/MM ordering.

SELECT make_timestamp(2024, 3, 15, 14, 30, 0) AS ts;
-- 2024-03-15 14:30:00

Seconds are double precision, so fractional values work too: make_timestamp(2024, 3, 15, 14, 30, 7.5). There are relatives as well: make_date(2024, 3, 15) for a bare date and make_time(14, 30, 0) for a time of day.

Compare this with the usual string concatenation, which is fragile and session-dependent:

-- fragile: depends on DateStyle and zero-padding
SELECT (y || '-' || m || '-' || d)::date FROM (SELECT 2024 y, 3 m, 15 d) s;

If m is 3 rather than 03, or DateStyle is set to DMY, that code silently returns the wrong date or errors out. make_timestamp removes both failure modes at once.

An interval from named arguments

make_interval builds an interval from named fields: years, months, weeks, days, hours, mins, secs. Pass only what you need; everything else defaults to zero.

SELECT make_interval(days => 10, hours => 2) AS shipping_window;
-- 10 days 02:00:00

The big win is parameterization. When the amount is a variable number, you cannot drop it into a string INTERVAL literal, but you can pass it straight to make_interval:

-- give every paid order a grace period of N days
SELECT o.id,
       o.created_at + make_interval(days => 14) AS grace_until
FROM orders o
WHERE o.status = 'paid';

That 14 could be a column or a $1 placeholder. This is the crucial difference from a string interval: INTERVAL '$1 days' does not work because the placeholder would sit inside the literal, whereas make_interval(days => $1) binds correctly.

-- per-row interval driven by data, not a constant string
SELECT u.id, u.email,
       u.created_at + make_interval(days => 30) AS trial_ends
FROM users u
WHERE u.country = 'DE';

make_timestamptz and the time zone

make_timestamp has a zone-aware sibling, make_timestamptz. An extra text argument names the zone in which the supplied numbers are interpreted; the result is a timestamptz.

SELECT make_timestamptz(2024, 3, 15, 14, 30, 0, 'Europe/Berlin') AS ts_tz;
-- stored as UTC, shown in your session zone

Without the final argument the numbers are read in the current session zone (TimeZone). That is handy for deadlines anchored to a specific region:

SELECT u.id,
       make_timestamptz(2024, 12, 31, 23, 59, 59, 'America/Sao_Paulo') AS cutoff
FROM users u
WHERE u.country = 'BR';

Gotcha: the make_* arguments do not wrap around. make_timestamp(2024, 13, 1, 0, 0, 0) does not roll into January 2025 — it raises field value out of range. The same goes for make_date(2024, 2, 30). If the inputs are untrusted numbers, validate them first or catch the exception.

Differences in other engines

make_timestamp, make_interval and friends are PostgreSQL. Other engines take a different route:

• MySQL: assemble a date with MAKEDATE/MAKETIME or STR_TO_DATE('2024-03-15','%Y-%m-%d'). Intervals use the INTERVAL 10 DAY syntax, and for a variable amount you write INTERVAL n DAY, where n can be an expression.
• ClickHouse: use makeDateTime(2024, 3, 15, 14, 30, 0) and makeDateTime64(...) for sub-second precision; the zone is a separate argument. Build an interval with toIntervalDay(n), toIntervalHour(n) and add them together.

If the code must be portable, isolate date assembly in one layer: the make_* family on PostgreSQL, native functions elsewhere. The core principle is the same everywhere: do not glue dates from strings when the engine can build them from numbers.