MAKE_INTERVAL

​

Syntax

MAKE_INTERVAL([<years> [, <months> [, <weeks> [, <days> [, <hours> [, <mins> [, <secs>]]]]]]])
MAKE_INTERVAL(<name> => <value> [, ...])

​

Parameters

​

Return type

​

Remarks

• Years become 12 months and weeks become 7 days. Hours, minutes, and seconds are added up into the time part, which is not converted into days: MAKE_INTERVAL(hours => 30) returns 30:00:00. Use JUSTIFY_HOURS to move whole days out of the time part.
• Seconds are rounded to the nearest microsecond. A value exactly halfway between two microseconds rounds to the even one.
• The result must fit the interval: months and days each fit a 32-bit integer, and the time part fits 64-bit microseconds (about 292,000 years). A larger result raises interval out of range.
• MAKE_INTERVAL is compatible with PostgreSQL’s make_interval, with two differences. PostgreSQL’s integer parameters take only INTEGER; Firebolt also accepts BIGINT. PostgreSQL 16 silently wraps a result that does not fit; Firebolt raises an error.
• An INTERVAL can be used in expressions and query results, but it can’t be stored in a table column.

​

Examples

SELECT MAKE_INTERVAL(1, 2, 3, 4, 5, 6, 7.5);

SELECT MAKE_INTERVAL(days => 10, hours => 3);

WITH orders(id, created, ship_days) AS (
  SELECT 1, TIMESTAMP '2024-03-01 09:00:00', 2::BIGINT
  UNION ALL SELECT 2, TIMESTAMP '2024-03-01 09:00:00', 10)
SELECT id, created + MAKE_INTERVAL(days => ship_days) AS due
FROM orders
ORDER BY id;