---
title: "to_years"
slug: "dev-database-reference-sql-functions-date-time-to-years"
updated: 2026-10-30T18:19:01Z
published: 2026-10-30T18:19:01Z
canonical: "docs.vastdata.com/dev-database-reference-sql-functions-date-time-to-years"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt
> Use this file to discover all available pages before exploring further.

# to_years

*Applies to VAST Cluster 5.5.*

*[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Date and time functions](/develop/docs/dev-database-reference-sql-functions-date-time) › Interval constructors.* Returns an interval of a whole number of years, stored as 12 months per year.

## Syntax

```
to_years(n)
```

| Argument | Type | Description |
| --- | --- | --- |
| `n` | `INTEGER` (or `SMALLINT`) | The number of years. Negative numbers give a negative interval. |

**Returns:** `INTERVAL`.

## Set up the examples

The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-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](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), 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 &apos;2024-02-29 00:00:00&apos; + to_years(1)` is 2025-02-28 00:00:00. A `DATE` plus the interval is a `TIMESTAMP`: `DATE &apos;2026-01-31&apos; + to_years(1)` is 2027-01-31 00:00:00.

## NULLs and edge cases

| Input | Result |
| --- | --- |
| `NULL`, `NULL::INTEGER` | `NULL` |
| `0` | An interval of 0 |
| `&apos;2&apos;` (a string of digits) | 24 months |
| `CAST(2 AS SMALLINT)` | 24 months |
| `178956970` | 2147483640 months |
| `178956971` | Error: `Interval value 178956971 years out of range` |
| `CAST(2 AS BIGINT)` | Error: `No function matches the given name and argument types &apos;to_years(BIGINT)&apos;`; cast it to `INTEGER` |
| `1.5` (a decimal) | Error: `No function matches the given name and argument types &apos;to_years(DECIMAL(2,1))&apos;` |
| `CAST(1.5 AS DOUBLE)` | Error: `No function matches the given name and argument types &apos;to_years(DOUBLE)&apos;` |
| A `DATE`, `INTERVAL` or boolean | Error: `No function matches the given name and argument types`, for example `&apos;to_years(INTERVAL)&apos;` |

The same cases, as the cluster returns them, with the examples from [What it does](/develop/docs/dev-database-reference-sql-functions-date-time-to-years#what-it-does). They use the helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-date-time-to-years#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 &apos;2027-01-01 00:00:00&apos; - 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 &lt; TIMESTAMP &apos;2027-01-01 00:00:00&apos; - to_years(1)` rather than `starts + to_years(1) &lt; TIMESTAMP &apos;2027-01-01 00:00:00&apos;`. 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 &apos;to_years(BIGINT)&apos;`; 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](/develop/docs/dev-database-reference-sql-functions-date-time-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](/develop/docs/dev-database-reference-sql-functions-date-time-epoch) counts a year as 365.25 days (`31557600.0` seconds for `to_years(1)`), and [epoch_us](/develop/docs/dev-database-reference-sql-functions-date-time-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

| Function | Result for `1` | Same as |
| --- | --- | --- |
| `to_years(1)` | 12 months | `INTERVAL 1 YEAR` |
| [to_months](/develop/docs/dev-database-reference-sql-functions-date-time-to-months)`(12)` | 12 months | `to_years(1)` |
| [to_quarters](/develop/docs/dev-database-reference-sql-functions-date-time-to-quarters)`(4)` | 12 months | `to_years(1)` |
| [to_decades](/develop/docs/dev-database-reference-sql-functions-date-time-to-decades)`(1)` | 120 months | `to_years(10)` |
| [to_centuries](/develop/docs/dev-database-reference-sql-functions-date-time-to-centuries)`(1)` | 1200 months | `to_years(100)` |
| [to_millennia](/develop/docs/dev-database-reference-sql-functions-date-time-to-millennia)`(1)` | 12000 months | `to_years(1000)` |

```
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)](/develop/docs/dev-database-reference-sql-functions-date-time-plus): add an interval to a date or timestamp.
- [date_add](/develop/docs/dev-database-reference-sql-functions-date-time-date-add): the function form of adding an interval.
- [Date and time functions](/develop/docs/dev-database-reference-sql-functions-date-time): all the date and time functions.
- [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns).