Welcome to the new DOCS.vastdata.com. We've added new menus and a version selector, plus new persona and resource pages to help customers get started. Looking for a previous article? Use the docs search.

Press CTRL+K to open search

date_add

Prev Next

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Adding and interval arithmetic. Adds an interval to a date, timestamp or time, or a number of days to a date. date_add(a, b) is a shorthand for a + b and works exactly like the + operator: adding a month to the end of a month gives the end of the shorter month, so 31 January plus one month is 28 February.

Syntax

date_add(value, interval)
date_add(date, days)
Argument Type Description
value DATE, TIMESTAMP (also TIMESTAMP_NS and TIMESTAMP_MS) or TIME The value to add to.
interval INTERVAL The time to add; it can be negative, and it can come from columns, as in to_days(n).
date, days DATE and INTEGER A number of days to add to a date; negative goes back.

The arguments can also be in the other order (date_add(INTERVAL 1 DAY, d)), or a DATE and a TIME, which gives the timestamp of that time on that day, as with +.

Returns:

Arguments Result
DATE, INTERVAL TIMESTAMP, also when the interval is whole days
TIMESTAMP (any precision), INTERVAL TIMESTAMP
TIME, INTERVAL TIME, wrapping past midnight
DATE, INTEGER DATE
DATE, TIME TIMESTAMP
INTERVAL, INTERVAL 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_date_add. 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_date_add"'

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

date_add adds the interval's months first, then its days, then its time:

  • Months and years move the calendar month and keep the day of the month. When that day doesn't exist in the new month, the result is the month's last day: 2026-01-31 plus one month is 2026-02-28, 2024-01-31 plus one month is 2024-02-29, and 2024-02-29 plus one year is 2025-02-28 (plus four years, 2028-02-29).
  • Days move whole calendar days: 2026-01-31 plus INTERVAL '1 month 1 day' is 2026-02-28 plus one day, 2026-03-01.
  • Time moves the clock. On a TIME it wraps: TIME '23:30:00' plus one hour is 00:30:00.

Add a total interval in one call rather than one month at a time: date_add(DATE '2026-01-31', INTERVAL 2 MONTH) is 2026-03-31, but adding one month and then another gives 2026-03-28, because the first step stops at 28 February.

NULLs and edge cases

Input Result
A NULL value (row 4), NULL::DATE, NULL::INTERVAL or a NULL number of days NULL
An untyped NULL value: date_add(NULL, INTERVAL 1 MONTH) Error: Could not choose a best candidate function for the function call "+("NULL", INTERVAL)"; use NULL::TIMESTAMP
A negative interval: INTERVAL '-1 month' on DATE '2026-01-01' 2025-12-01
Mixed signs: INTERVAL '1 month -1 day' on DATE '2026-03-01' 2026-03-31
A TIMESTAMP_NS value A TIMESTAMP: the nanoseconds are dropped
A date past the supported range: date_add(DATE '2026-01-01', 2147483647) Error: Date out of range
A fraction of a day: date_add(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: date_add(ts, 1) Error: No function matches the given name and argument types '+(TIMESTAMP, INTEGER_LITERAL)'; add INTERVAL 1 DAY
A string value, such as 'abc' Error: Could not choose a best candidate function for the function call "+(STRING_LITERAL, INTERVAL)"; cast it
Three arguments, as in date_add('day', 1, d) Error: Macro function date_add(date, interval) requires 2 positional arguments, but 3 positional arguments were provided.

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

run(f"SELECT id, date_add(d, INTERVAL 1 MONTH), date_add(d, n) FROM {TABLE} WHERE id = 4")
run("SELECT date_add(NULL::DATE, INTERVAL 1 DAY), date_add(DATE '2026-01-01', NULL::INTERVAL), "
    "date_add(DATE '2026-01-01', INTERVAL '-1 month'), date_add(DATE '2026-03-01', INTERVAL '1 month -1 day'), "
    "CAST(date_add(TIMESTAMP_NS '2026-01-01 00:00:00.000000001', INTERVAL 1 DAY) AS VARCHAR)")
run("SELECT date_add(NULL, INTERVAL 1 MONTH)")
run("SELECT date_add(DATE '2026-01-01', 2147483647)")
run("SELECT date_add(DATE '2026-01-01', 1.5)")
run("SELECT date_add(TIMESTAMP '2026-01-01 00:00:00', 1)")
run("SELECT date_add('abc', INTERVAL 1 MONTH)")
run("SELECT date_add('day', 1, DATE '2026-01-01')")

[(4, None, None)]
[(None, None, datetime.datetime(2025, 12, 1, 0, 0), datetime.datetime(2026, 3, 31, 0, 0), '2026-01-02 00:00:00')]
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
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: Could not choose a best candidate function for the function call "+(STRING_LITERAL, INTERVAL)". In order to select one, please add explicit type casts.
InternalError: Binder Error: Macro function date_add(date, interval) requires 2 positional arguments, but 3 positional arguments were provided.

The month-end and leap-day cases from What it does, and adding a total against adding one month at a time:

run("SELECT date_add(DATE '2026-01-31', INTERVAL 1 MONTH), date_add(DATE '2024-01-31', INTERVAL 1 MONTH), "
    "date_add(DATE '2026-05-31', INTERVAL 1 MONTH), date_add(DATE '2024-02-29', INTERVAL 1 YEAR), "
    "date_add(DATE '2024-02-29', INTERVAL 4 YEAR), date_add(DATE '2026-01-31', INTERVAL '1 month 1 day')")
run("SELECT date_add(DATE '2026-01-31', INTERVAL 2 MONTH), date_add(DATE '2026-01-31', INTERVAL 1 MONTH + INTERVAL 1 MONTH), "
    "DATE '2026-01-31' + INTERVAL 1 MONTH + INTERVAL 1 MONTH, date_add(TIME '23:30:00', INTERVAL 1 HOUR)")

[(datetime.datetime(2026, 2, 28, 0, 0), datetime.datetime(2024, 2, 29, 0, 0), datetime.datetime(2026, 6, 30, 0, 0), datetime.datetime(2025, 2, 28, 0, 0), datetime.datetime(2028, 2, 29, 0, 0), datetime.datetime(2026, 3, 1, 0, 0))]
[(datetime.datetime(2026, 3, 31, 0, 0), datetime.datetime(2026, 3, 31, 0, 0), datetime.datetime(2026, 3, 28, 0, 0), datetime.time(0, 30))]

Examples

One month after each date, and n days after it:

run(f"SELECT id, date_add(d, INTERVAL 1 MONTH), date_add(d, n) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, datetime.datetime(2026, 3, 28, 0, 0), datetime.date(2026, 3, 1)), (2, datetime.datetime(2026, 4, 1, 0, 0), datetime.date(2026, 2, 27)), (3, datetime.datetime(2025, 1, 30, 0, 0), datetime.date(2024, 12, 30)), (4, None, None), (5, datetime.datetime(2024, 3, 29, 0, 0), datetime.date(2024, 3, 12))]

Ninety minutes after each timestamp and an hour after each time of day; then n days after each timestamp, with the interval built from the column:

run(f"SELECT id, date_add(ts, INTERVAL 90 MINUTE), date_add(t, INTERVAL 1 HOUR) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, date_add(ts, to_days(n)) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, datetime.datetime(2026, 3, 1, 0, 45), datetime.time(0, 15)), (2, datetime.datetime(2026, 3, 1, 10, 0, 45, 123456), datetime.time(9, 30, 45, 123456)), (3, datetime.datetime(2024, 12, 30, 13, 30), datetime.time(13, 0)), (4, None, None), (5, datetime.datetime(2024, 2, 29, 1, 30), datetime.time(1, 0))]
[(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))]

Each date at its time of day, as one timestamp:

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

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

Keep only the events whose next day starts after 1 March 2026:

run(f"SELECT id FROM {TABLE} WHERE date_add(d, INTERVAL 1 DAY) > TIMESTAMP '2026-03-01 00:00:00' ORDER BY id LIMIT 10")

[(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 date_add() on a column is always computed by the Query Engine:

  • In the SELECT list, the scan reads the column and the Query Engine computes date_add() for each row it returns.
  • In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes date_add() 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 date_add(d, INTERVAL 1 DAY) > TIMESTAMP '2026-03-01 00:00:00'")

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

Make it faster

  • Move the arithmetic to the constant side. d > DATE '2026-03-01' - 1 keeps the same rows as date_add(d, INTERVAL 1 DAY) > TIMESTAMP '2026-03-01 00:00:00', and it's pushed down to VAST DataBase, so rows that don't match never leave storage. The constant side is computed once. Its plan has no FILTER step:
run(f"SELECT id FROM {TABLE} WHERE d > DATE '2026-03-01' - 1 ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE d > DATE '2026-03-01' - 1")

[(2,)]
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
  • For timestamps, subtract the interval from the constant: ts > TIMESTAMP '2026-03-01 00:00:00' - INTERVAL 1 HOUR is pushed down, date_add(ts, INTERVAL 1 HOUR) > ... isn't (-).

Limitations and errors

  • Write a negative interval in quotes, or negate it. INTERVAL -1 DAY is a syntax error (syntax error at or near "DAY"). Write INTERVAL '-1 day' or -INTERVAL 1 DAY.
  • Days only on a DATE. A number of days works with a DATE; for a TIMESTAMP or a fraction of a day, add an INTERVAL.
  • No time-zone support. On a TIMESTAMP WITH TIME ZONE value, such as now(), date_add fails with No function matches the given name and argument types '+(TIMESTAMP WITH TIME ZONE, INTERVAL)'. Cast the value to TIMESTAMP first; see time zones.
  • Other SQL dialects' names and forms aren't available. dateadd fails with Scalar Function with name dateadd does not exist!, and date_add('day', 1, d) with three arguments fails too. Use date_add(d, INTERVAL 1 DAY) or d + INTERVAL 1 DAY.
run("SELECT date_add(DATE '2026-03-01', INTERVAL -1 DAY)")
run("SELECT date_add(DATE '2026-03-01', INTERVAL '-1 day'), date_add(DATE '2026-03-01', -INTERVAL 1 DAY)")
run("SELECT date_add(now(), INTERVAL 1 DAY)")
run("SELECT date_add(CAST(now() AS TIMESTAMP), INTERVAL 1 DAY) > CAST(now() AS TIMESTAMP)")
run("SELECT dateadd('day', 1, DATE '2026-03-01')")

InternalError: Parse query: 'SELECT date_add(DATE '2026-03-01', INTERVAL -1 DAY)' failed with: Parser Error: syntax error at or near "DAY"
[(datetime.datetime(2026, 2, 28, 0, 0), datetime.datetime(2026, 2, 28, 0, 0))]
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: Catalog Error: Scalar Function with name dateadd does not exist!

Similar functions

Function or operator Result for DATE '2026-01-31' and one month
+, add() The same: d + INTERVAL 1 MONTH is 2026-02-28 00:00:00
-, subtract() Goes back: d - INTERVAL 1 MONTH; the same as date_add(d, -INTERVAL 1 MONTH)
to_months and the other interval constructors Build the interval from a number or a column: d + to_months(1)
time_bucket, date_trunc Round a value down instead of moving it 
run("SELECT date_add(DATE '2026-01-31', INTERVAL 1 MONTH), DATE '2026-01-31' + INTERVAL 1 MONTH, "
    "add(DATE '2026-01-31', INTERVAL 1 MONTH), DATE '2026-01-31' + to_months(1), DATE '2026-01-31' + INTERVAL 30 DAY")
run("SELECT DATE '2026-03-31' - INTERVAL 1 MONTH, date_add(DATE '2026-03-31', -INTERVAL 1 MONTH)")

[(datetime.datetime(2026, 2, 28, 0, 0), datetime.datetime(2026, 2, 28, 0, 0), datetime.datetime(2026, 2, 28, 0, 0), datetime.datetime(2026, 2, 28, 0, 0), datetime.datetime(2026, 3, 2, 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()

[]
[]

See also

Related articles
  • + (add a date or interval)
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > Date and time functions
  • - (subtract a date or interval)
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > Date and time functions
  • date_diff
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > Date and time functions