to_years

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Interval constructors. Returns an interval of a whole number of years, stored as 12 months per year.

Syntax

to_years(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_to_years. 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 subscriptions, each with a start (starts) and a term in years (years, an INTEGER). Row 1 starts on 29 February, row 3 has a negative term, and 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_to_years"'

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}.subscriptions"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, starts TIMESTAMP, years INTEGER)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, TIMESTAMP '2024-02-29 00:00:00', 1), "
    "(2, TIMESTAMP '2026-01-31 09:00:00', 2), "
    "(3, TIMESTAMP '2025-06-15 12:00:00', -1), "
    "(4, NULL, NULL), "
    "(5, TIMESTAMP '2026-03-01 00:00:00', 0)")

[]
[]
[(5,)]

What it does

to_years(n) is the same interval as INTERVAL n YEAR: to_years(1) = INTERVAL 1 YEAR is true. An interval keeps months, days and time separately, and a year is stored as 12 months, so to_years(2) is an interval of 24 months, and to_years(-1) of -12 months.

Unlike the literal, to_years takes a column or an expression, so each row can have its own number of years.

Adding the interval to a timestamp moves it by whole months, to the same day and time. When that day doesn't exist, the result is the last day of the month: TIMESTAMP '2024-02-29 00:00:00' + to_years(1) is 2025-02-28 00:00:00. A DATE plus the interval is a TIMESTAMP: DATE '2026-01-31' + to_years(1) is 2027-01-31 00:00:00.

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 to_years(1) = INTERVAL 1 YEAR, to_years(2), to_years(-1), "
    "TIMESTAMP '2024-02-29 00:00:00' + to_years(1), DATE '2026-01-31' + to_years(1)")
run("SELECT to_years(NULL), to_years(0), to_years('2'), to_years(CAST(2 AS SMALLINT)), to_years(178956970)")
run("SELECT to_years(178956971)")
run("SELECT to_years(CAST(2 AS BIGINT))")
run("SELECT to_years(1.5)")
run("SELECT to_years(CAST(1.5 AS DOUBLE))")
run("SELECT to_years(INTERVAL 1 DAY)")

[(True, MonthDayNano(months=24, days=0, nanoseconds=0), MonthDayNano(months=-12, days=0, nanoseconds=0), datetime.datetime(2025, 2, 28, 0, 0), datetime.datetime(2027, 1, 31, 0, 0))]
[(None, MonthDayNano(months=0, days=0, nanoseconds=0), MonthDayNano(months=24, days=0, nanoseconds=0), MonthDayNano(months=24, days=0, nanoseconds=0), MonthDayNano(months=2147483640, days=0, nanoseconds=0))]
OSError: Interval value 178956971 years out of range
InternalError: Binder Error: No function matches the given name and argument types 'to_years(BIGINT)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'to_years(DECIMAL(2,1))'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'to_years(DOUBLE)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'to_years(INTERVAL)'. You might need to add explicit type casts.

Examples

The end of each subscription. Row 1 ends on 28 February 2025, because 2025 has no 29 February; row 3's negative term moves it back a year:

run(f"SELECT id, starts, to_years(years), starts + to_years(years) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, datetime.datetime(2024, 2, 29, 0, 0), MonthDayNano(months=12, days=0, nanoseconds=0), datetime.datetime(2025, 2, 28, 0, 0)), (2, datetime.datetime(2026, 1, 31, 9, 0), MonthDayNano(months=24, days=0, nanoseconds=0), datetime.datetime(2028, 1, 31, 9, 0)), (3, datetime.datetime(2025, 6, 15, 12, 0), MonthDayNano(months=-12, days=0, nanoseconds=0), datetime.datetime(2024, 6, 15, 12, 0)), (4, None, None, None), (5, datetime.datetime(2026, 3, 1, 0, 0), MonthDayNano(months=0, days=0, nanoseconds=0), datetime.datetime(2026, 3, 1, 0, 0))]

Keep the subscriptions that end before 2027:

run(f"SELECT id FROM {TABLE} WHERE starts + to_years(years) < TIMESTAMP '2027-01-01 00:00:00' ORDER BY id LIMIT 10")

[(1,), (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 a function of a column, so to_years() of a column, and a column plus it, are always computed by the Query Engine:

• In the SELECT list, the scan reads the columns and the Query Engine computes the interval and the sum 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.

On constants, such as TIMESTAMP '2027-01-01 00:00:00' - to_years(1), the Query Engine computes the value once, before the scan.

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

plan(f"SELECT id FROM {TABLE} WHERE starts + to_years(years) < TIMESTAMP '2027-01-01 00:00:00'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ ((starts + to_years(years)│
│ ) < '2027-01-01 00:00:00':│
│        :TIMESTAMP)        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           starts          │
│           years           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Move the interval to the constant side. When the number of years is the same for every row, write starts < TIMESTAMP '2027-01-01 00:00:00' - to_years(1) rather than starts + to_years(1) < TIMESTAMP '2027-01-01 00:00:00'. The bound is computed once and compared with the raw column, which is pushed down to VAST DataBase.
• Store the end if you filter on it often. When each row has its own term, compute starts + to_years(years) when you write the rows and keep it in its own TIMESTAMP column.

plan(f"SELECT id FROM {TABLE} WHERE starts < TIMESTAMP '2027-01-01 00:00:00' - to_years(1)")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

Limitations and errors

• INTEGER only. A BIGINT fails with No function matches the given name and argument types 'to_years(BIGINT)'; cast it: to_years(CAST(n AS INTEGER)). Integer literals such as 2 are INTEGER already.
• Whole years. A decimal or DOUBLE fails. For part of a year, use to_months (to_months(18) is a year and a half).
• A year is 12 months, not a number of days. How long it is in seconds depends on the function that measures it: epoch counts a year as 365.25 days (31557600.0 seconds for to_years(1)), and epoch_us as 12 months of 30 days (31104000000000 microseconds).
• Range. Up to ±178956970 years (2147483640 months); beyond, Interval value ... years out of range.

run("SELECT to_years(CAST(CAST(2 AS BIGINT) AS INTEGER))")
run("SELECT epoch(to_years(1)), epoch_us(to_years(1))")

[(MonthDayNano(months=24, days=0, nanoseconds=0),)]
[(31557600.0, 31104000000000)]

Similar functions

run("SELECT to_years(1), to_months(12), to_quarters(4), to_decades(1), to_centuries(1), to_millennia(1), "
    "to_years(1) = to_months(12)")

[(MonthDayNano(months=12, days=0, nanoseconds=0), MonthDayNano(months=12, days=0, nanoseconds=0), MonthDayNano(months=12, days=0, nanoseconds=0), MonthDayNano(months=120, days=0, nanoseconds=0), MonthDayNano(months=1200, days=0, nanoseconds=0), MonthDayNano(months=12000, days=0, nanoseconds=0), True)]

Clean up:

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

[]
[]

See also

• + (plus): add an interval to a date or timestamp.
• date_add: the function form of adding an interval.
• Date and time functions: all the date and time functions.
• Where filters run and pushdowns.