+ (add a date or interval)

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Adding and interval arithmetic. Moves a date, timestamp or time forward by an interval, adds a number of days to a date, joins a date and a time into a timestamp, or adds two intervals. add(a, b) is the same function, and date_add is a shorthand for it. For adding numbers, see + (addition).

Syntax

date + days
days + date
date + time
value + interval
interval + value
interval + interval
add(a, b)

Argument	Type	Description
`date`	`DATE`	A date.
`days`	`INTEGER` or `SMALLINT`	A number of days; negative goes back. A `BIGINT` doesn't work: cast it to `INTEGER`.
`time`	`TIME`	A time of day, joined to `date` to make a timestamp.
`value`	`DATE`, `TIMESTAMP` (also `TIMESTAMP_NS` and `TIMESTAMP_MS`) or `TIME`	The value to move.
`interval`	`INTERVAL`	The time to add, such as `INTERVAL 1 MONTH`, `INTERVAL '1 day 2 hours'` or `to_days(n)`.

Returns:

Expression	Result
`DATE + INTEGER`, `INTEGER + DATE`	`DATE`
`DATE + TIME`, `TIME + DATE`	`TIMESTAMP`
`DATE + INTERVAL`, `INTERVAL + DATE`	`TIMESTAMP`, also when the interval is whole days
`TIMESTAMP + INTERVAL`, `INTERVAL + TIMESTAMP`	`TIMESTAMP`, for every timestamp precision
`TIME + INTERVAL`, `INTERVAL + TIME`	`TIME`, wrapping past midnight
`INTERVAL + INTERVAL`	`INTERVAL`

Aliases: add(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_plus. 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 date (d), a timestamp (ts), a time of day (t) and a number of days (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_plus"'

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, d DATE, ts TIMESTAMP, t TIME, n INTEGER)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, DATE '2026-02-28', TIMESTAMP '2026-02-28 23:15:00', TIME '23:15:00', 1), "
    "(2, DATE '2026-03-01', TIMESTAMP '2026-03-01 08:30:45.123456', TIME '08:30:45.123456', -2), "
    "(3, DATE '2024-12-30', TIMESTAMP '2024-12-30 12:00:00', TIME '12:00:00', 0), "
    "(4, NULL, NULL, NULL, NULL), "
    "(5, DATE '2024-02-29', TIMESTAMP '2024-02-29 00:00:00', TIME '00:00:00', 12)")

[]
[]
[(5,)]

What it does

An INTERVAL has three separate fields, months, days and time, and + applies them in that order:

Months and years move the calendar month and keep the day of the month; when the day doesn't exist, the result is the month's last day. DATE '2026-01-31' + INTERVAL 1 MONTH is 2026-02-28, and DATE '2024-02-29' + INTERVAL 1 YEAR is 2025-02-28.
Days move whole days: DATE '2026-01-30' + INTERVAL '1 month 1 day' is 2026-02-28 plus one day, 2026-03-01.
Time moves the clock: TIMESTAMP '2024-02-29 23:59:59' + INTERVAL 1 SECOND is 2024-03-01 00:00:00. INTERVAL '1.5 days' is one day and twelve hours.

On a TIME, only the time field counts and the result wraps: TIME '23:30:00' + INTERVAL 1 HOUR is 00:30:00, and adding a day or a month leaves a time unchanged.

There are no time zones, so there are no daylight-saving jumps: TIMESTAMP '2026-03-29 01:30:00' + INTERVAL 1 HOUR is 02:30:00.

INTERVAL + INTERVAL adds field by field and doesn't convert between them: INTERVAL 1 MONTH + INTERVAL 30 DAY is one month and 30 days. + is evaluated left to right, so d + INTERVAL 1 MONTH + INTERVAL 1 MONTH moves one month at a time (2026-01-31 gives 2026-03-28), while d + INTERVAL 2 MONTH gives 2026-03-31.

NULLs and edge cases

Input	Result
A NULL value (row 4), `NULL::DATE + 1`, `DATE '2026-01-01' + NULL`, `ts + NULL::INTERVAL`	`NULL`
An untyped `NULL`: `NULL + INTERVAL 1 MONTH`	Error: `Could not choose a best candidate function for the function call "+("NULL", INTERVAL)"`; use `NULL::TIMESTAMP`
`DATE 'infinity' + 1`, `DATE 'infinity' + INTERVAL 1 DAY`	`infinity`
`DATE '9999-12-31' + 1`	`10000-01-01`, which Python's `datetime` can't hold: `CAST` it to `VARCHAR`
A date or timestamp past the supported range: `DATE '2026-01-01' + 2147483647`, `ts + INTERVAL 300000 YEAR`	Error: `Date out of range`; `Date and time not in timestamp range`
Intervals that overflow: `INTERVAL '2147483647 months' + INTERVAL 1 MONTH`	Error: `Overflow in addition of INT32 (2147483647 + 1)!`
A `BIGINT` number of days: `d + id`	Error: `No function matches the given name and argument types '+(DATE, BIGINT)'`; use `d + CAST(id AS INTEGER)`
A fraction of a day: `DATE '2026-01-01' + 1.5`	Error: `No function matches the given name and argument types '+(DATE, DECIMAL(2,1))'`; add `INTERVAL 36 HOUR`
A number added to a `TIMESTAMP` or `TIME`: `ts + 1`	Error: `No function matches the given name and argument types '+(TIMESTAMP, INTEGER_LITERAL)'`; add an `INTERVAL`
Two dates, two times, or a `TIMESTAMP` and a `TIME`	Error: `No function matches the given name and argument types`, for example `'+(DATE, DATE)'`
A string: `DATE '2026-01-01' + '1'`, `'abc' + INTERVAL 1 MONTH`	Error: `Could not choose a best candidate function`; cast it
Unary `+` on a date or an interval: `+INTERVAL 1 DAY`	Error: `No function matches the given name and argument types '+(INTERVAL)'`

The same cases, as the cluster returns them. They use the table and helpers from Set up the examples:

run(f"SELECT id, d + n, ts + INTERVAL 1 MONTH FROM {TABLE} WHERE id = 4")
run("SELECT NULL::DATE + 1, DATE '2026-01-01' + NULL, TIMESTAMP '2026-01-01 00:00:00' + NULL::INTERVAL, "
    "CAST(DATE 'infinity' + 1 AS VARCHAR), CAST(DATE 'infinity' + INTERVAL 1 DAY AS VARCHAR), "
    "CAST(DATE '9999-12-31' + 1 AS VARCHAR)")
run("SELECT NULL + INTERVAL 1 MONTH")
run("SELECT DATE '2026-01-01' + 2147483647")
run("SELECT TIMESTAMP '2026-01-01 00:00:00' + INTERVAL 300000 YEAR")
run("SELECT INTERVAL '2147483647 months' + INTERVAL 1 MONTH")
run(f"SELECT id, d + id FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, d + CAST(id AS INTEGER) FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT DATE '2026-01-01' + 1.5")
run("SELECT TIMESTAMP '2026-01-01 00:00:00' + 1")
run("SELECT DATE '2026-01-01' + DATE '2026-01-01'")
run("SELECT DATE '2026-01-01' + '1'")
run("SELECT +INTERVAL 1 DAY")

[(4, None, None)]
[(None, None, None, 'infinity', 'infinity', '10000-01-01')]
InternalError: Binder Error: Could not choose a best candidate function for the function call "+("NULL", INTERVAL)". In order to select one, please add explicit type casts.
OSError: Date out of range
OSError: Date and time not in timestamp range
OSError: Overflow in addition of INT32 (2147483647 + 1)!
InternalError: Binder Error: No function matches the given name and argument types '+(DATE, BIGINT)'. You might need to add explicit type casts.
[(1, datetime.date(2026, 3, 1)), (2, datetime.date(2026, 3, 3)), (3, datetime.date(2025, 1, 2)), (4, None), (5, datetime.date(2024, 3, 5))]
InternalError: Binder Error: No function matches the given name and argument types '+(DATE, DECIMAL(2,1))'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types '+(TIMESTAMP, INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types '+(DATE, DATE)'. You might need to add explicit type casts.
InternalError: Binder Error: Could not choose a best candidate function for the function call "+(DATE, STRING_LITERAL)". In order to select one, please add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types '+(INTERVAL)'. You might need to add explicit type casts.

The month-end, leap-day, time and interval cases from What it does:

run("SELECT DATE '2026-01-31' + INTERVAL 1 MONTH, DATE '2024-02-29' + INTERVAL 1 YEAR, "
    "DATE '2026-01-30' + INTERVAL '1 month 1 day', TIMESTAMP '2024-02-29 23:59:59' + INTERVAL 1 SECOND, "
    "TIMESTAMP '2026-03-01 00:00:00' + INTERVAL '1.5 days'")
run("SELECT TIME '23:30:00' + INTERVAL 1 HOUR, TIME '23:59:59' + INTERVAL 1 DAY, TIME '08:00:00' + INTERVAL 1 MONTH, "
    "TIMESTAMP '2026-03-29 01:30:00' + INTERVAL 1 HOUR")
run("SELECT INTERVAL 1 MONTH + INTERVAL 30 DAY, DATE '2026-01-31' + INTERVAL 1 MONTH + INTERVAL 1 MONTH, "
    "DATE '2026-01-31' + INTERVAL 2 MONTH")

[(datetime.datetime(2026, 2, 28, 0, 0), datetime.datetime(2025, 2, 28, 0, 0), datetime.datetime(2026, 3, 1, 0, 0), datetime.datetime(2024, 3, 1, 0, 0), datetime.datetime(2026, 3, 2, 12, 0))]
[(datetime.time(0, 30), datetime.time(23, 59, 59), datetime.time(8, 0), datetime.datetime(2026, 3, 29, 2, 30))]
[(MonthDayNano(months=1, days=30, nanoseconds=0), datetime.datetime(2026, 3, 28, 0, 0), datetime.datetime(2026, 3, 31, 0, 0))]

Examples

The next day, n days later, and each date at its time of day:

run(f"SELECT id, d + 1, d + n, d + t FROM {TABLE} ORDER BY id LIMIT 10")

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

A month after each timestamp, a month and a day after it, and n days after it, with the interval built from the column in two ways:

run(f"SELECT id, ts + INTERVAL 1 MONTH, INTERVAL '1 month 1 day' + ts FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, ts + INTERVAL (n) DAY, ts + to_days(n) FROM {TABLE} ORDER BY id LIMIT 10")

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

Ninety minutes after each time of day, and the same with add():

run(f"SELECT id, t + INTERVAL 90 MINUTE, add(t, INTERVAL 90 MINUTE), add(d, n) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, datetime.time(0, 45), datetime.time(0, 45), datetime.date(2026, 3, 1)), (2, datetime.time(10, 0, 45, 123456), datetime.time(10, 0, 45, 123456), datetime.date(2026, 2, 27)), (3, datetime.time(13, 30), datetime.time(13, 30), datetime.date(2024, 12, 30)), (4, None, None, None), (5, datetime.time(1, 30), datetime.time(1, 30), datetime.date(2024, 3, 12))]

Keep only the events whose timestamp plus one hour is after 1 March 2026:

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

[(1,), (2,)]

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 adds to it for each row it returns.
In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes the sum and keeps the rows that match. No rows are skipped in storage.
On constants only, as in TIMESTAMP '2026-01-15 00:00:00' + INTERVAL 1 DAY, the sum is computed once, and a comparison of a column with it is pushed down.

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

plan(f"SELECT id FROM {TABLE} WHERE ts + INTERVAL 1 HOUR > TIMESTAMP '2026-03-01 00:00:00'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│     ((ts + '01:00:00':    │
│  :INTERVAL) > '2026-03-01 │
│    00:00:00'::TIMESTAMP)  │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             ts            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

Move the interval to the constant side. ts > TIMESTAMP '2026-03-01 00:00:00' - INTERVAL 1 HOUR keeps the same rows as ts + INTERVAL 1 HOUR > TIMESTAMP '2026-03-01 00:00:00', and it's pushed down to VAST DataBase, so rows that don't match never leave storage. Its plan has no FILTER step:

run(f"SELECT id FROM {TABLE} WHERE ts > TIMESTAMP '2026-03-01 00:00:00' - INTERVAL 1 HOUR ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE ts > TIMESTAMP '2026-03-01 00:00:00' - INTERVAL 1 HOUR")

[(1,), (2,)]
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

The same for days on a date: write d > DATE '2026-02-28' - 1, not d + 1 > DATE '2026-02-28'. A constant built with +, such as TIMESTAMP '2026-01-15 00:00:00' + to_days(30), is pushed down as well:

plan(f"SELECT id FROM {TABLE} WHERE d > DATE '2026-02-28' - 1")
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      │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

Limitations and errors

No time-zone support. + with a TIMESTAMP WITH TIME ZONE value, such as now(), fails with No function matches the given name and argument types '+(TIMESTAMP WITH TIME ZONE, INTERVAL)'. Cast the value to TIMESTAMP first: CAST(now() AS TIMESTAMP) + INTERVAL 1 DAY. See time zones.
Fractions in interval literals need quotes. INTERVAL 1.5 DAY is a syntax error; write INTERVAL '1.5 days' or INTERVAL 36 HOUR.
Nanoseconds are dropped. TIMESTAMP_NS plus an interval is a TIMESTAMP, with microseconds.

run("SELECT now() + INTERVAL 1 DAY")
run("SELECT CAST(now() AS TIMESTAMP) + INTERVAL 1 DAY > CAST(now() AS TIMESTAMP)")
run("SELECT TIMESTAMP '2026-03-01 00:00:00' + INTERVAL 1.5 DAY")
run("SELECT CAST(TIMESTAMP_NS '2026-01-01 00:00:00.000000001' + INTERVAL 1 DAY AS VARCHAR)")

InternalError: Binder Error: No function matches the given name and argument types '+(TIMESTAMP WITH TIME ZONE, INTERVAL)'. You might need to add explicit type casts.
[(True,)]
InternalError: Parse query: 'SELECT TIMESTAMP '2026-03-01 00:00:00' + INTERVAL 1.5 DAY' failed with: Parser Error: syntax error at or near "1.5"
[('2026-01-02 00:00:00',)]

Similar functions

Function or operator	What it does	Result for `DATE '2026-01-31'`
`+ INTERVAL 1 MONTH`	Adds one calendar month	`2026-02-28 00:00:00`
`+ INTERVAL 30 DAY`	Adds 30 days	`2026-03-02 00:00:00`
`+ 1`	Adds one day, keeping a `DATE`	`2026-02-01`
date_add, `add()`	The same as `+`	`2026-02-28 00:00:00`
-	Goes back, or gives the time between two values
to_months, to_days	Build an interval from a number or a column	`+ to_months(1)` is `2026-02-28 00:00:00`

run("SELECT DATE '2026-01-31' + INTERVAL 1 MONTH, DATE '2026-01-31' + INTERVAL 30 DAY, DATE '2026-01-31' + 1, "
    "date_add(DATE '2026-01-31', INTERVAL 1 MONTH), add(DATE '2026-01-31', INTERVAL 1 MONTH), DATE '2026-01-31' + to_months(1)")

[(datetime.datetime(2026, 2, 28, 0, 0), datetime.datetime(2026, 3, 2, 0, 0), datetime.date(2026, 2, 1), datetime.datetime(2026, 2, 28, 0, 0), datetime.datetime(2026, 2, 28, 0, 0), datetime.datetime(2026, 2, 28, 0, 0))]

Clean up:

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

[]
[]