> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

# MAKE_INTERVAL

> Reference material for the MAKE_INTERVAL function

Builds an `INTERVAL` from a count of years, months, weeks, days, hours, minutes, and seconds. Each argument is optional and defaults to 0, so you can pass only the parts you need, by position or by name.

## Syntax

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
MAKE_INTERVAL([<years> [, <months> [, <weeks> [, <days> [, <hours> [, <mins> [, <secs>]]]]]]])
MAKE_INTERVAL(<name> => <value> [, ...])
```

## Parameters

| Parameter | Description | Supported input types |
| :- | :- | :- |
| `<years>` | The number of years. Defaults to 0. | `INTEGER`, `BIGINT` |
| `<months>` | The number of months. Defaults to 0. | `INTEGER`, `BIGINT` |
| `<weeks>` | The number of weeks. Defaults to 0. | `INTEGER`, `BIGINT` |
| `<days>` | The number of days. Defaults to 0. | `INTEGER`, `BIGINT` |
| `<hours>` | The number of hours. Defaults to 0. | `INTEGER`, `BIGINT` |
| `<mins>` | The number of minutes. Defaults to 0. | `INTEGER`, `BIGINT` |
| `<secs>` | The number of seconds, with an optional fractional part. Defaults to 0. | `DOUBLE PRECISION` |

Pass arguments by position, by name (`days => 5`), or both. Positional arguments come first and fill the parameters in the order listed. Each name may appear once.

## Return type

`INTERVAL`. If any argument is `NULL`, the result is `NULL`.

## 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](https://docs.firebolt.io/reference-sql/functions-reference/date-and-time/justify.md) 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

**Positional arguments**

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT MAKE_INTERVAL(1, 2, 3, 4, 5, 6, 7.5);
```

**Returns**: `1 year 2 mons 25 days 05:06:07.5`

**Named arguments**

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT MAKE_INTERVAL(days => 10, hours => 3);
```

**Returns**: `10 days 03:00:00`

**Per-row values**

Arguments can be columns, so each row gets its own interval:

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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;
```

| id | due |
| :- | :- |
| 1 | 2024-03-03 09:00:00 |
| 2 | 2024-03-11 09:00:00 |

```text theme={"theme":{"light":"css-variables","dark":"css-variables"}}
MAKE_INTERVAL(years, months, weeks, days, hours, mins, secs) -> INTERVAL
- Parameter types: years..mins BIGINT (INTEGER widens implicitly), secs DOUBLE PRECISION.
  NUMERIC/DOUBLE for years..mins is rejected (no implicit narrowing). An untyped
  string literal is cast to the parameter type: MAKE_INTERVAL('5') = 5 years.
- All parameters default to 0; named (name => value) and positional may mix,
  positional first. Unknown name, duplicate name, more than 7 arguments, or a
  positional argument after a named one is a signature error.
- NULL in any argument -> NULL.
- months = years*12 + months; days = weeks*7 + days;
  microseconds = (hours*3600 + mins*60)*1e6 + rint(secs*1e6), half to even.
  No carry between fields: MAKE_INTERVAL(hours => 30) = 30:00:00.
- Overflow (months or days outside int32, microseconds outside int64, secs NaN
  or infinite) -> error "interval out of range".
- PostgreSQL: same results; Firebolt additionally accepts BIGINT and errors on
  overflow where PostgreSQL 16 wraps.
- INTERVAL cannot be stored in a table column (CREATE TABLE, CTAS).
- Examples:
  MAKE_INTERVAL(mins => 90) = 01:30:00
  MAKE_INTERVAL(secs => 0.0000025) = 00:00:00.000002
  TIMESTAMP '2024-01-31 10:00:00' + MAKE_INTERVAL(months => 1, hours => 2) = 2024-02-29 12:00:00
  MAKE_INTERVAL(years => 200000000) -> interval out of range
```


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.