- (subtract 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 back by an interval or a number of days, returns the days between two dates or the interval between two timestamps, subtracts one interval from another, or negates an interval. subtract(a, b) and subtract(interval) are the same function. For subtracting numbers, see - (subtraction).

Syntax

date - days
date - date
value - interval
timestamp - timestamp
interval - interval
-interval
subtract(a, b)
subtract(interval)

Returns:

Aliases: subtract(a, b), subtract(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_minus. 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_minus"'

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

Going back in time. Subtracting an interval works like adding its negative (+): months first, then days, then time. When the day of the month doesn't exist in the new month, the result is the month's last day: DATE '2026-03-31' - INTERVAL 1 MONTH is 2026-02-28, and DATE '2024-03-31' - INTERVAL 1 MONTH is 2024-02-29. On a TIME the result wraps: TIME '00:30:00' - INTERVAL 1 HOUR is 23:30:00.

The time between two values.

• DATE - DATE is a whole number of days: DATE '2026-03-01' - DATE '2024-02-29' is 731.
• TIMESTAMP - TIMESTAMP is an interval in days and time, without months: TIMESTAMP '2026-03-01 00:00:00' - TIMESTAMP '2024-02-29 12:00:00' is 730 days and 12 hours. For a result in months and days, use age.

Both are negative when the second value is later.

Intervals. INTERVAL - INTERVAL subtracts field by field and doesn't convert between months, days and time: INTERVAL 1 MONTH - INTERVAL 30 DAY is one month and -30 days, not zero. -interval negates every field.

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, d - n, DATE '2026-12-31' - d, ts - INTERVAL 1 MONTH FROM {TABLE} WHERE id = 4")
run("SELECT NULL::DATE - 1, DATE '2026-01-01' - NULL::DATE, -NULL::INTERVAL, "
    "TIMESTAMP '2026-03-01 00:00:00' - INTERVAL '-1 month', CAST(DATE '0001-01-01' - 1 AS VARCHAR), "
    "DATE 'infinity' - DATE '2026-01-01', "
    "CAST(TIMESTAMP_NS '2026-01-01 00:00:00.000000002' - TIMESTAMP_NS '2026-01-01 00:00:00.000000001' AS VARCHAR)")
run("SELECT NULL - INTERVAL 1 MONTH")
run("SELECT TIMESTAMP 'infinity' - TIMESTAMP '2026-01-01 00:00:00'")
run("SELECT -INTERVAL '-2147483648 months'")
run("SELECT TIME '09:30:00' - TIME '08:00:00'")
run("SELECT DATE '2026-03-01' - (isodow(DATE '2026-03-01') - 1)")
run("SELECT TIMESTAMP '2026-01-01 00:00:00' - 1")
run("SELECT 1 - DATE '2026-01-01'")

[(4, None, None, None)]
[(None, None, None, datetime.datetime(2026, 4, 1, 0, 0), '0001-12-31 (BC)', 2147463193, '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: Cannot subtract infinite timestamps
OSError: Overflow in negation of integer!
InternalError: Binder Error: No function matches the given name and argument types '-(TIME, TIME)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types '-(DATE, BIGINT)'. 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 '-(INTEGER_LITERAL, DATE)'. You might need to add explicit type casts.

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

run("SELECT DATE '2026-03-31' - INTERVAL 1 MONTH, DATE '2024-03-31' - INTERVAL 1 MONTH, TIME '00:30:00' - INTERVAL 1 HOUR, "
    "DATE '2026-03-01' - DATE '2024-02-29', DATE '2024-02-29' - DATE '2026-03-01'")
run("SELECT TIMESTAMP '2026-03-01 00:00:00' - TIMESTAMP '2024-02-29 12:00:00', "
    "TIMESTAMP '2026-03-01 12:00:00' - DATE '2026-03-01'")
run("SELECT INTERVAL 1 MONTH - INTERVAL 30 DAY, INTERVAL 1 DAY - INTERVAL 25 HOUR, -INTERVAL '1 month 2 days 03:00:00', "
    "subtract(INTERVAL '1 month 2 days')")

[(datetime.datetime(2026, 2, 28, 0, 0), datetime.datetime(2024, 2, 29, 0, 0), datetime.time(23, 30), 731, -731)]
[(MonthDayNano(months=0, days=730, nanoseconds=43200000000000), MonthDayNano(months=0, days=0, nanoseconds=43200000000000))]
[(MonthDayNano(months=1, days=-30, nanoseconds=0), MonthDayNano(months=0, days=1, nanoseconds=-90000000000000), MonthDayNano(months=-1, days=-2, nanoseconds=-10800000000000), MonthDayNano(months=-1, days=-2, nanoseconds=0))]

Examples

The day before each date, n days before it, and a month before it:

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

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

The days from each date to the end of 2026, and the interval from each timestamp to the end of 2026:

run(f"SELECT id, DATE '2026-12-31' - d, TIMESTAMP '2026-12-31 00:00:00' - ts FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 306, MonthDayNano(months=0, days=305, nanoseconds=2700000000000)), (2, 305, MonthDayNano(months=0, days=304, nanoseconds=55754876544000)), (3, 731, MonthDayNano(months=0, days=730, nanoseconds=43200000000000)), (4, None, None), (5, 1036, MonthDayNano(months=0, days=1036, nanoseconds=0))]

How far into its day each timestamp is, and ninety minutes before each time of day, also with subtract():

run(f"SELECT id, ts - d, t - INTERVAL 90 MINUTE, subtract(t, INTERVAL 90 MINUTE) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, MonthDayNano(months=0, days=0, nanoseconds=83700000000000), datetime.time(21, 45), datetime.time(21, 45)), (2, MonthDayNano(months=0, days=0, nanoseconds=30645123456000), datetime.time(7, 0, 45, 123456), datetime.time(7, 0, 45, 123456)), (3, MonthDayNano(months=0, days=0, nanoseconds=43200000000000), datetime.time(10, 30), datetime.time(10, 30)), (4, None, None, None), (5, MonthDayNano(months=0, days=0, nanoseconds=0), datetime.time(22, 30), datetime.time(22, 30))]

Keep only the events more than 300 days before the end of 2026:

run(f"SELECT id FROM {TABLE} WHERE DATE '2026-12-31' - d > 300 ORDER BY id LIMIT 10")

[(1,), (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 subtracts 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 difference and keeps the rows that match. No rows are skipped in storage.
• On constants only, as in DATE '2026-12-31' - 300, the difference 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 DATE '2026-12-31' - d > 300")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (('2026-12-31'::DATE - d) │
│           > 300)          │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             d             │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Subtract from the constant, not from the column. d < DATE '2026-12-31' - 300 keeps the same rows as DATE '2026-12-31' - d > 300, 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 d < DATE '2026-12-31' - 300 ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE d < DATE '2026-12-31' - 300")

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

• The same for timestamps. ts > TIMESTAMP '2026-03-01 00:00:00' - INTERVAL 1 HOUR is pushed down; TIMESTAMP '2026-12-31 00:00:00' - ts > INTERVAL 300 DAY isn't. For "in the last seven days", write ts > CAST(now() AS TIMESTAMP) - INTERVAL 7 DAY (now):

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

┌───────────────────────────┐
│         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.
• No TIME - TIME. For the time between two times of day, use date_diff with 'minute' or 'second'.
• Timestamp differences have no months. TIMESTAMP - TIMESTAMP counts days; use age for months and days.

run("SELECT now() - INTERVAL 1 DAY")
run("SELECT CAST(now() AS TIMESTAMP) - TIMESTAMP '2026-01-01 00:00:00' > INTERVAL 1 DAY")
run("SELECT date_diff('minute', TIME '08:00:00', TIME '09:30:00')")

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,)]
[(90,)]

Similar functions

run("SELECT DATE '2026-03-01' - DATE '2026-01-31', TIMESTAMP '2026-03-01 00:00:00' - TIMESTAMP '2026-01-31 00:00:00', "
    "age(DATE '2026-03-01', DATE '2026-01-31'), date_diff('month', DATE '2026-01-31', DATE '2026-03-01'), "
    "date_sub('month', DATE '2026-01-31', DATE '2026-03-01'), subtract(DATE '2026-03-01', DATE '2026-01-31')")

[(29, MonthDayNano(months=0, days=29, nanoseconds=0), MonthDayNano(months=1, days=1, nanoseconds=0), 2, 1, 29)]

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.
• - (subtraction): subtracting numbers.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.