year

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Parts of a date or time. Returns the calendar year of a date or timestamp, or the years field of an interval.

Syntax

year(value)

Returns: BIGINT.

Same as: date_part('year', value) and extract(year FROM value); see date_part.

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_year. 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 has a date (d), a timestamp (ts), a time of day (t) and a timestamp with nanoseconds (tns). Row 4 has no values, and row 5 is the last moment of 1969:

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

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, tns TIMESTAMP_NS)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, DATE '2026-02-28', TIMESTAMP '2026-02-28 23:15:00', TIME '23:15:00', "
    "CAST('2026-02-28 23:15:00.123456789' AS TIMESTAMP_NS)), "
    "(2, DATE '2026-03-01', TIMESTAMP '2026-03-01 08:30:45.123456', TIME '08:30:45.123456', "
    "CAST('2026-03-01 08:30:45.123456' AS TIMESTAMP_NS)), "
    "(3, DATE '2024-12-30', TIMESTAMP '2024-12-30 12:00:00', TIME '12:00:00', "
    "CAST('2024-12-30 12:00:00' AS TIMESTAMP_NS)), "
    "(4, NULL, NULL, NULL, NULL), "
    "(5, DATE '1969-12-31', TIMESTAMP '1969-12-31 23:59:59.999999', TIME '23:59:59.999999', "
    "CAST('1969-12-31 23:59:59.999999999' AS TIMESTAMP_NS))")

[]
[]
[(5,)]

What it does

year returns the calendar year of a date or timestamp: year(DATE '2024-02-29') is 2024. It works on every timestamp precision (TIMESTAMP, TIMESTAMP_NS, TIMESTAMP_MS, TIMESTAMP_S).

Years before year 1 count down from 0: 44 BC is -43.

On an INTERVAL, year returns the whole years in its months field, without normalising days: year(INTERVAL '30 months') is 2, and year(INTERVAL '400 days') is 0. A negative interval gives a negative year: year(INTERVAL '-13 months') is -1.

year is the calendar year. Around New Year, the ISO year can differ: 30 December 2024 is in calendar year 2024 but in ISO week 1 of 2025. For that, use isoyear.

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, year(ts) FROM {TABLE} WHERE id = 4")
run("SELECT year(DATE 'infinity'), year(TIMESTAMP '-infinity'), year(DATE '0001-01-01'), year(DATE '9999-12-31'), "
    "year(CAST('0044-03-15 (BC)' AS DATE)), year(DATE '2024-12-30')")
run("SELECT year(INTERVAL '1 year 2 months 3 days 04:05:06.789'), year(INTERVAL '30 months'), "
    "year(INTERVAL '-13 months'), year(INTERVAL '400 days')")
run("SELECT year(NULL)")
run("SELECT year(TIME '13:45:30')")
run("SELECT year('2024-02-29')")
run("SELECT year(123)")

[(4, None)]
[(None, None, 1, 9999, -43, 2024)]
[(1, 2, -1, 0)]
InternalError: Binder Error: Could not choose a best candidate function for the function call "year("NULL")". In order to select one, please add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'year(TIME)'. You might need to add explicit type casts.
InternalError: Binder Error: Could not choose a best candidate function for the function call "year(STRING_LITERAL)". In order to select one, please add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'year(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The year of each event's date and timestamp:

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

[(1, 2026, 2026), (2, 2026, 2026), (3, 2024, 2024), (4, None, None), (5, 1969, 1969)]

Count the events in each year. GROUP BY isn't available, so count each year with a FILTER clause. Row 4 has no timestamp, so its year is NULL:

run(f"SELECT count(*) FILTER (WHERE year(ts) = 2026), count(*) FILTER (WHERE year(ts) = 2024), "
    f"count(*) FILTER (WHERE year(ts) IS NULL) FROM {TABLE}")

[(2, 1, 1)]

Keep only the events from 2026. This works, but Make it faster has a faster way to write it:

run(f"SELECT id FROM {TABLE} WHERE year(ts) = 2026 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 a function of a column, so year() is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes year() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes year() and keeps the rows that match. No rows are skipped in storage. This is the same for a DATE column.

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

plan(f"SELECT id FROM {TABLE} WHERE year(ts) = 2026")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│    ("year"(ts) = 2026)    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             ts            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Filter on a range of the raw column, not on year(). ts >= TIMESTAMP '2026-01-01 00:00:00' AND ts < TIMESTAMP '2027-01-01 00:00:00' returns the same rows as year(ts) = 2026, and it's pushed down to VAST DataBase, so rows from other years never leave storage. The plan for the range has no FILTER step, and neither has the same range on the DATE column:

plan(f"SELECT id FROM {TABLE} WHERE ts >= TIMESTAMP '2026-01-01 00:00:00' AND ts < TIMESTAMP '2027-01-01 00:00:00'")
plan(f"SELECT id FROM {TABLE} WHERE d >= DATE '2026-01-01' AND d < DATE '2027-01-01'")

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

• Use year() in the SELECT list, where it doesn't stop a filter from being pushed down.
• Sort the table on its time column. Sorting lets the scan skip whole blocks of rows outside the range; see indexing and data skipping.

Limitations and errors

• No time-zone support. On a TIMESTAMP WITH TIME ZONE value, such as now(), year fails with an Extension Autoloading Error. Cast the value to TIMESTAMP first: year(CAST(now() AS TIMESTAMP)). See time zones.
• Not for TIME. A time of day has no year; year(TIME ...) fails with No function matches the given name and argument types 'year(TIME)'.
• TIMESTAMP_NS just before 1970. Row 5's tns, 1969-12-31 23:59:59.999999999, gives 1970. Store such values as TIMESTAMP if you need their year; row 5's ts gives 1969.
• One argument. year(ts, ts) fails with No function matches the given name and argument types 'year(TIMESTAMP, TIMESTAMP)'.

run(f"SELECT id, year(tns), year(ts) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT year(ts, ts) FROM {TABLE} LIMIT 1")

[(1, 2026, 2026), (2, 2026, 2026), (3, 2024, 2024), (4, None, None), (5, 1970, 1969)]
InternalError: Binder Error: No function matches the given name and argument types 'year(TIMESTAMP, TIMESTAMP)'. You might need to add explicit type casts.

Similar functions

run("SELECT year(DATE '2024-12-30'), isoyear(DATE '2024-12-30'), yearweek(DATE '2024-12-30'), "
    "date_trunc('year', DATE '2024-12-30'), strftime(DATE '2024-12-30', '%Y')")
run("SELECT decade(DATE '2024-12-30'), century(DATE '2024-12-30'), millennium(DATE '2024-12-30'), era(DATE '2024-12-30')")

[(2024, 2025, 202501, datetime.date(2024, 1, 1), '2024')]
[(202, 21, 3, 1)]

Clean up:

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

[]
[]

See also

• date_part: any part of a value, by name.
• Date and time functions: all the date and time functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.