---
title: "* (multiply an interval)"
slug: "dev-database-reference-sql-functions-date-time-multiply"
updated: 2026-10-30T18:19:01Z
published: 2026-10-30T18:19:01Z
canonical: "docs.vastdata.com/dev-database-reference-sql-functions-date-time-multiply"
---

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

# * (multiply an interval)

*Applies to VAST Cluster 5.5.*

*[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Date and time functions](/develop/docs/dev-database-reference-sql-functions-date-time) › Adding and interval arithmetic.* Multiplies an interval by a whole number: `INTERVAL 1 DAY * 3` is three days. Use it to build an interval from a column, as in `ts + n * INTERVAL 1 HOUR`. `multiply(a, b)` is the same function. For multiplying numbers, see [* (multiplication)](/develop/docs/dev-database-reference-sql-functions-operators-multiplication).

## Syntax

```
interval * n
n * interval
multiply(interval, n)
multiply(n, interval)
```

| Argument | Type | Description |
| --- | --- | --- |
| `interval` | `INTERVAL` | The interval to multiply. |
| `n` | `INTEGER` or `BIGINT` | The factor; it can be zero or negative, and it can be a column. |

**Returns:** `INTERVAL`.

**Aliases:** `multiply(a, b)`.

## Set up the examples

The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-adbc), and work in a schema called `docs_sql_fn_date_time_multiply`. Set `QE_ENDPOINT`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` and `VASTDB_BUCKET` ([SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the `run()` and `plan()` helpers, and creates a table of five events, each with a timestamp (`ts`) and a count (`n`, an `INTEGER`). Row 4 has no values:

```
import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_date_time_multiply"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line))

TABLE = f"{SCHEMA}.events"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, ts TIMESTAMP, n INTEGER)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, TIMESTAMP '2026-02-28 23:15:00', 1), "
    "(2, TIMESTAMP '2026-03-01 08:30:45.123456', -2), "
    "(3, TIMESTAMP '2024-12-30 12:00:00', 0), "
    "(4, NULL, NULL), "
    "(5, TIMESTAMP '2024-02-29 00:00:00', 12)")
```

```
[]
[]
[(5,)]
```

## What it does

An `INTERVAL` has three separate fields, months, days and time, and `*` multiplies each one by `n` without converting between them: `INTERVAL &apos;1 month 1 day 01:00:00&apos; * 2` is 2 months, 2 days and 2 hours. A negative `n` makes every field negative, and `0` gives an empty interval.

The factor can be on either side (`3 * INTERVAL 1 DAY`), and it can be a column (`INTEGER` or `BIGINT`), so each row can get its own interval. Adding the product to a timestamp moves it by `n` units: `ts + n * INTERVAL 1 HOUR` is `n` hours later.

## NULLs and edge cases

| Input | Result |
| --- | --- |
| A NULL factor (row 4), `INTERVAL 1 DAY * NULL`, `NULL::INTERVAL * 3` | `NULL` |
| `INTERVAL 1 DAY * 0` | An interval of `0` |
| `INTERVAL 1 MONTH * 2147483647` | 2147483647 months |
| A factor past the range of a month count: `INTERVAL 1 MONTH * 2147483648` | Error: `Type INT64 with value 2147483648 can&apos;t be cast because the value is out of range for the destination type INT32` |
| A fraction: `INTERVAL 1 DAY * 1.5`, or a `DOUBLE` | Error: `No function matches the given name and argument types &apos;*(INTERVAL, DECIMAL(2,1))&apos;`; multiply a smaller unit: `INTERVAL 1 HOUR * 36` |
| A date, timestamp or time: `DATE &apos;2026-01-01&apos; * 3` | Error: `No function matches the given name and argument types &apos;*(DATE, INTEGER_LITERAL)&apos;` |

The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-date-time-multiply#set-up-the-examples):

```
run(f"SELECT id, n * INTERVAL 1 HOUR FROM {TABLE} WHERE id = 4")
run("SELECT INTERVAL 1 DAY * NULL, NULL::INTERVAL * 3, INTERVAL 1 DAY * 0, INTERVAL 1 MONTH * 2147483647")
run("SELECT INTERVAL 1 MONTH * 2147483648")
run("SELECT INTERVAL 1 DAY * 1.5")
run("SELECT INTERVAL 1 DAY * CAST(1.5 AS DOUBLE)")
run("SELECT INTERVAL 1 HOUR * 36")
run("SELECT DATE '2026-01-01' * 3")
```

```
[(4, None)]
[(None, None, MonthDayNano(months=0, days=0, nanoseconds=0), MonthDayNano(months=2147483647, days=0, nanoseconds=0))]
OSError: Type INT64 with value 2147483648 can't be cast because the value is out of range for the destination type INT32
InternalError: Binder Error: No function matches the given name and argument types '*(INTERVAL, DECIMAL(2,1))'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types '*(INTERVAL, DOUBLE)'. You might need to add explicit type casts.
[(MonthDayNano(months=0, days=0, nanoseconds=129600000000000),)]
InternalError: Binder Error: No function matches the given name and argument types '*(DATE, INTEGER_LITERAL)'. You might need to add explicit type casts.
```

Each field multiplied, a negative factor, and the factor on either side:

```
run("SELECT INTERVAL '1 month 1 day 01:00:00' * 2, INTERVAL 1 DAY * -1, INTERVAL 1 DAY * 3, 3 * INTERVAL 1 DAY, "
    "multiply(INTERVAL 1 MONTH, -2), typeof(INTERVAL 1 DAY * 3)")
```

```
[(MonthDayNano(months=2, days=2, nanoseconds=7200000000000), MonthDayNano(months=0, days=-1, nanoseconds=0), MonthDayNano(months=0, days=3, nanoseconds=0), MonthDayNano(months=0, days=3, nanoseconds=0), MonthDayNano(months=-2, days=0, nanoseconds=0), 'INTERVAL')]
```

## Examples

An interval of `n` hours for each row, and of `id` days (`id` is a `BIGINT`):

```
run(f"SELECT id, n * INTERVAL 1 HOUR, INTERVAL 1 DAY * id, multiply(n, INTERVAL 1 HOUR) FROM {TABLE} ORDER BY id LIMIT 10")
```

```
[(1, MonthDayNano(months=0, days=0, nanoseconds=3600000000000), MonthDayNano(months=0, days=1, nanoseconds=0), MonthDayNano(months=0, days=0, nanoseconds=3600000000000)), (2, MonthDayNano(months=0, days=0, nanoseconds=-7200000000000), MonthDayNano(months=0, days=2, nanoseconds=0), MonthDayNano(months=0, days=0, nanoseconds=-7200000000000)), (3, MonthDayNano(months=0, days=0, nanoseconds=0), MonthDayNano(months=0, days=3, nanoseconds=0), MonthDayNano(months=0, days=0, nanoseconds=0)), (4, None, MonthDayNano(months=0, days=4, nanoseconds=0), None), (5, MonthDayNano(months=0, days=0, nanoseconds=43200000000000), MonthDayNano(months=0, days=5, nanoseconds=0), MonthDayNano(months=0, days=0, nanoseconds=43200000000000))]
```

Each timestamp moved by `n` days:

```
run(f"SELECT id, ts + n * INTERVAL 1 DAY FROM {TABLE} ORDER BY id LIMIT 10")
```

```
[(1, datetime.datetime(2026, 3, 1, 23, 15)), (2, datetime.datetime(2026, 2, 27, 8, 30, 45, 123456)), (3, datetime.datetime(2024, 12, 30, 12, 0)), (4, None), (5, datetime.datetime(2024, 3, 12, 0, 0))]
```

Keep only the events that are still before 1 March 2026 after moving `n` days:

```
run(f"SELECT id FROM {TABLE} WHERE ts + n * INTERVAL 1 DAY < TIMESTAMP '2026-03-01 00:00:00' ORDER BY id LIMIT 10")
```

```
[(2,), (3,), (5,)]
```

## How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (`VAST_SCAN`). VAST DataBase can evaluate a comparison of a column with a constant, but not an expression on a column, so `*` on a column is always computed by the Query Engine:

- **In the `SELECT` list,** the scan reads the column and the Query Engine multiplies for each row it returns.
- **In `WHERE`,** the scan sends the columns for every row, and a `FILTER` step in the Query Engine computes the expression and keeps the rows that match. No rows are skipped in storage.

The plan of the `WHERE` example shows the `FILTER` step above the scan:

```
plan(f"SELECT id FROM {TABLE} WHERE ts + n * INTERVAL 1 DAY < TIMESTAMP '2026-03-01 00:00:00'")
```

```
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ ((ts + (CAST(n AS BIGINT) │
│  * '1 day'::INTERVAL)) <  │
│   '2026-03-01 00:00:00':  │
│        :TIMESTAMP)        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             ts            │
│             n             │
│             id            │
│                           │
└───────────────────────────┘
```

## Make it faster

- **Keep the column alone on one side of the comparison.** When the interval doesn't depend on a column, put it with the constant: an expression of constants is computed once, and the comparison of the column with it is pushed down to VAST DataBase, so rows that don't match never leave storage. The plan of `ts &gt; TIMESTAMP &apos;2026-01-15 00:00:00&apos; + to_days(30)` has no `FILTER` step:

```
plan(f"SELECT id FROM {TABLE} WHERE ts > TIMESTAMP '2026-01-15 00:00:00' + to_days(30)")
```

```
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
```

- **Store the result if you filter on it often.** When the interval depends on a column, as `n * INTERVAL 1 DAY` does, compute `ts + n * INTERVAL 1 DAY` when you write the rows and keep it in its own column.

## Limitations and errors

- **Whole numbers only.** A `DECIMAL` or `DOUBLE` factor fails with `No function matches the given name and argument types`. Multiply a smaller unit (`INTERVAL 1 HOUR * 36` for a day and a half), or build the interval with [to_seconds](/develop/docs/dev-database-reference-sql-functions-date-time-to-seconds), which takes a fraction.
- **No multiplying dates, timestamps or times.** `*` takes an interval and an integer. To divide an interval by a number, see [/](/develop/docs/dev-database-reference-sql-functions-date-time-divide).
- **A month count must fit in a 32-bit integer.** A product with more months fails with `Type INT64 with value ... can&apos;t be cast because the value is out of range for the destination type INT32`.

```
run("SELECT INTERVAL 1 HOUR * 36, to_seconds(1.5) = INTERVAL '1.5 seconds'")
```

```
[(MonthDayNano(months=0, days=0, nanoseconds=129600000000000), True)]
```

## Similar functions

| Function or expression | Result for 3 hours |
| --- | --- |
| `INTERVAL 1 HOUR * 3`, `3 * INTERVAL 1 HOUR`, `multiply(INTERVAL 1 HOUR, 3)` | 3 hours |
| `INTERVAL (3) HOUR` | 3 hours: the number in parentheses can be a column |
| [to_hours](/develop/docs/dev-database-reference-sql-functions-date-time-to-hours)`(3)` and the other interval constructors | 3 hours, from a number or a column |
| [/](/develop/docs/dev-database-reference-sql-functions-date-time-divide) | Divides an interval by an integer |
| [+](/develop/docs/dev-database-reference-sql-functions-date-time-plus) | Adds the interval to a date, timestamp or time |

```
run("SELECT INTERVAL 1 HOUR * 3, 3 * INTERVAL 1 HOUR, multiply(INTERVAL 1 HOUR, 3), INTERVAL (3) HOUR, to_hours(3), "
    "INTERVAL 1 HOUR * 3 = to_hours(3)")
run(f"SELECT id, n * INTERVAL 1 HOUR = INTERVAL (n) HOUR, n * INTERVAL 1 HOUR = to_hours(n) FROM {TABLE} ORDER BY id LIMIT 10")
```

```
[(MonthDayNano(months=0, days=0, nanoseconds=10800000000000), MonthDayNano(months=0, days=0, nanoseconds=10800000000000), MonthDayNano(months=0, days=0, nanoseconds=10800000000000), MonthDayNano(months=0, days=0, nanoseconds=10800000000000), MonthDayNano(months=0, days=0, nanoseconds=10800000000000), True)]
[(1, True, True), (2, True, True), (3, True, True), (4, None, None), (5, True, True)]
```

Clean up:

```
run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()
```

```
[]
[]
```

## See also

- [Date and time functions](/develop/docs/dev-database-reference-sql-functions-date-time): all the date and time functions.
- [* (multiplication)](/develop/docs/dev-database-reference-sql-functions-operators-multiplication): multiplying numbers.
- [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns).
- [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.