/ (divide an interval)

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Adding and interval arithmetic. Divides an interval by a whole number. The months, days and time of day are divided separately, and each drops its remainder.

Syntax

interval / n

Returns: INTERVAL.

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_divide. 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.

A table can't have an INTERVAL column, so the examples build intervals from two timestamps: ended - started is an interval of days and time. This block connects, defines the run() and plan() helpers, and creates a table of five trips, each split into a number of legs. Row 4 has no values, and row 5 has no legs:

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_divide"'

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}.trips"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, started TIMESTAMP, ended TIMESTAMP, legs BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, TIMESTAMP '2026-03-01 08:00:00', TIMESTAMP '2026-03-02 09:00:00', 2), "
    "(2, TIMESTAMP '2026-03-01 08:00:00', TIMESTAMP '2026-03-03 08:00:00', 4), "
    "(3, TIMESTAMP '2026-03-01 08:00:00', TIMESTAMP '2026-03-01 08:00:01', 3), "
    "(4, NULL, NULL, NULL), "
    "(5, TIMESTAMP '2026-03-01 08:00:00', TIMESTAMP '2026-03-01 10:00:00', 0)")

[]
[]
[(5,)]

What it does

An interval has three fields: months, days, and a time (stored in nanoseconds). / divides each field by n on its own, as a whole-number division, and drops the remainder. Nothing carries over from one field to the next:

• INTERVAL 3 DAY / 2 is 1 day, and INTERVAL 1 DAY / 2 is 0: half a day isn't carried into the time.
• INTERVAL 1 MONTH / 2 is 0: half a month isn't carried into the days.
• INTERVAL 25 HOUR / 2 is 12 hours 30 minutes: hours are part of the time field, which divides down to the microsecond.
• INTERVAL '1 month 1 day 01:00:00' / 2 is 30 minutes: the month and the day both divide to 0.

To split a duration evenly whatever its fields, convert it to seconds first; see Similar functions.

NULLs and edge cases

The same cases, as the cluster returns them, with the examples from What it does. They use the helpers from Set up the examples:

run("SELECT INTERVAL 3 DAY / 2, INTERVAL 1 DAY / 2, INTERVAL 1 MONTH / 2, INTERVAL 25 HOUR / 2, "
    "INTERVAL '1 month 1 day 01:00:00' / 2")
run("SELECT INTERVAL 1 DAY / NULL, NULL::INTERVAL / 2, INTERVAL 1 DAY / 0, INTERVAL 1 DAY / -1, INTERVAL 1 SECOND / 3")
run("SELECT INTERVAL 1 DAY / 2.0")
run("SELECT INTERVAL 1 DAY / CAST(2 AS DOUBLE)")
run("SELECT INTERVAL 1 DAY / INTERVAL 1 HOUR")
run("SELECT 2 / INTERVAL 1 DAY")
run("SELECT DATE '2026-01-01' / 2")

[(MonthDayNano(months=0, days=1, nanoseconds=0), MonthDayNano(months=0, days=0, nanoseconds=0), MonthDayNano(months=0, days=0, nanoseconds=0), MonthDayNano(months=0, days=0, nanoseconds=45000000000000), MonthDayNano(months=0, days=0, nanoseconds=1800000000000))]
[(None, None, None, MonthDayNano(months=0, days=-1, nanoseconds=0), MonthDayNano(months=0, days=0, nanoseconds=333333000))]
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.
InternalError: Binder Error: No function matches the given name and argument types '/(INTERVAL, INTERVAL)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types '/(INTEGER_LITERAL, INTERVAL)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types '/(DATE, INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The length of each leg. ended - started is in days and time, so trip 1 (1 day 1 hour, 2 legs) gives 30 minutes, not 12 hours 30 minutes: the day divides to 0 and the hour to 30 minutes. Trip 2 (2 days, 4 legs) gives 0, and trip 5 (0 legs) gives NULL:

run(f"SELECT id, ended - started, (ended - started) / legs FROM {TABLE} ORDER BY id LIMIT 10")

[(1, MonthDayNano(months=0, days=1, nanoseconds=3600000000000), MonthDayNano(months=0, days=0, nanoseconds=1800000000000)), (2, MonthDayNano(months=0, days=2, nanoseconds=0), MonthDayNano(months=0, days=0, nanoseconds=0)), (3, MonthDayNano(months=0, days=0, nanoseconds=1000000000), MonthDayNano(months=0, days=0, nanoseconds=333333000)), (4, None, None), (5, MonthDayNano(months=0, days=0, nanoseconds=7200000000000), None)]

Keep the trips whose legs are longer than an hour:

run(f"SELECT id FROM {TABLE} WHERE (ended - started) / legs > INTERVAL 1 HOUR ORDER BY id LIMIT 10")

[]

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 of columns, so / on an interval is always computed by the Query Engine:

• In the SELECT list, the scan reads the columns and the Query Engine computes the interval and divides it 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 (ended - started) / legs > INTERVAL 1 HOUR")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #3            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│   (normalized_interval((  │
│ (ended - started) / legs))│
│   > '01:00:00'::INTERVAL) │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           ended           │
│          started          │
│            legs           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Store the duration if you filter on it often. Compute it when you write the rows, for example as whole seconds in a BIGINT column. A comparison of that column with a constant is pushed down to VAST DataBase, so rows that don't match never leave storage; (ended - started) / legs > INTERVAL 1 HOUR isn't.
• Combine it with a pushed-down condition. In started >= TIMESTAMP '2026-03-01 00:00:00' AND (ended - started) / legs > INTERVAL 1 HOUR, the comparison of started with a constant is still evaluated in VAST DataBase, so the division runs only on the rows that pass it.

Limitations and errors

• Whole-number divisors only. A decimal or DOUBLE divisor fails with No function matches the given name and argument types, for example '/(INTERVAL, DOUBLE)'. To divide by a fraction, convert the interval to seconds with epoch, divide, and convert back with to_seconds.
• Each field drops its remainder. See What it does. It matters for any interval with days or months, including the difference of two timestamps, which is in days and time. Divide the epoch value instead.
• No interval by interval. To get the ratio of two intervals, divide their epoch values.
• No function form, no //. divide(INTERVAL 1 DAY, 2) fails with No function matches the given name and argument types 'divide(INTERVAL, INTEGER_LITERAL)', and INTERVAL 1 DAY // 2 with '//(INTERVAL, INTEGER_LITERAL)'. Use /.

run("SELECT divide(INTERVAL 1 DAY, 2)")
run("SELECT INTERVAL 1 DAY // 2")

InternalError: Binder Error: No function matches the given name and argument types 'divide(INTERVAL, INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types '//(INTERVAL, INTEGER_LITERAL)'. You might need to add explicit type casts.

Similar functions

run("SELECT INTERVAL 1 DAY / 2, epoch(INTERVAL 1 DAY) / 2, to_seconds(epoch(INTERVAL 1 DAY) / 2), INTERVAL 1 DAY * 2")
run(f"SELECT id, to_seconds(epoch(ended - started) / legs) FROM {TABLE} WHERE legs > 0 ORDER BY id LIMIT 10")

[(MonthDayNano(months=0, days=0, nanoseconds=0), 43200.0, MonthDayNano(months=0, days=0, nanoseconds=43200000000000), MonthDayNano(months=0, days=2, nanoseconds=0))]
[(1, MonthDayNano(months=0, days=0, nanoseconds=45000000000000)), (2, MonthDayNano(months=0, days=0, nanoseconds=43200000000000)), (3, MonthDayNano(months=0, days=0, nanoseconds=333333000))]

Clean up:

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

[]
[]

See also

• + (plus) and - (minus): add an interval to a date or timestamp, and the difference of two timestamps.
• Date and time functions: all the date and time functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.