* (multiply an interval)

Applies to VAST Cluster 5.5.

Functions › Date and time functions › 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).

Syntax

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

Returns: INTERVAL.

Aliases: multiply(a, b).

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (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), 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 '1 month 1 day 01:00:00' * 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

The same cases, as the cluster returns them. They use the table and helpers from 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 > TIMESTAMP '2026-01-15 00:00:00' + 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, which takes a fraction.
• No multiplying dates, timestamps or times. * takes an interval and an integer. To divide an interval by a number, see /.
• A month count must fit in a 32-bit integer. A product with more months fails with Type INT64 with value ... can'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

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: all the date and time functions.
• * (multiplication): multiplying numbers.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.