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

> ## 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.

# age

*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) › Differences.* Returns the time from one timestamp to another as an `INTERVAL` in calendar terms: months (a year is 12 of them), days, and a time of day. `age(TIMESTAMP '2026-12-31 00:00:00', TIMESTAMP '2026-02-28 23:15:00')` is 10 months, 2 days and 45 minutes. With one argument, it's the age of the value at midnight at the start of today.

## Syntax

```
age(end, start)
age(value)
```

| Argument | Type | Description |
| --- | --- | --- |
| `end`, `start` | `TIMESTAMP` (also `TIMESTAMP_NS` and `TIMESTAMP_MS`), or `DATE` | The two values. Dates are read as midnight. The result is positive when `end` is after `start`. |
| `value` | `TIMESTAMP` or `DATE` | With one argument: the age of `value` at midnight at the start of today, the same as `age(today at midnight, value)`. |

**Returns:** `INTERVAL`. The Python driver returns it as `MonthDayNano(months=..., days=..., nanoseconds=...)`; `CAST(... AS VARCHAR)` gives it as text.

## 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_age`. 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 events, each with a date (`d`) and a timestamp (`ts`). 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_age"'

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)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, DATE '2026-02-28', TIMESTAMP '2026-02-28 23:15:00'), "
    "(2, DATE '2026-03-01', TIMESTAMP '2026-03-01 08:30:45.123456'), "
    "(3, DATE '2024-12-30', TIMESTAMP '2024-12-30 12:00:00'), "
    "(4, NULL, NULL), "
    "(5, DATE '2024-02-29', TIMESTAMP '2024-02-29 00:00:00')")
```

```
[]
[]
[(5,)]
```

## What it does

`age(end, start)` steps from `start` to `end` in whole months first, then whole days, then the time that's left. The result keeps those three fields apart:

| `end` | `start` | Result |
| --- | --- | --- |
| `2026-12-31 00:00:00` | `2026-02-28 23:15:00` | 10 months, 2 days, 45 minutes |
| `2026-12-31 00:00:00` | `2024-12-30 12:00:00` | 24 months, 12 hours |
| `2026-03-01` | `2026-01-31` | 1 month, 1 day |
| `2026-02-28` | `2026-01-31` | 28 days |
| `2025-02-28` | `2024-02-29` | 11 months, 28 days |
| `2028-02-29` | `2024-02-29` | 48 months |
| `2026-01-31` | `2026-03-01` | -1 month, -1 day |

Years appear as 12 months each: two years is `months=24`. When `end` is before `start`, every field is negative.

`age(value)` measures from midnight at the start of today, so its result changes from one day to the next.

## NULLs and edge cases

| Input | Result |
| --- | --- |
| A NULL value (row 4), or `NULL::TIMESTAMP` as either argument | `NULL` |
| `TIMESTAMP &apos;infinity&apos;` | `NULL` |
| Two `TIMESTAMP_NS` values 1 nanosecond apart | An interval of `0` |
| `DATE` arguments | Work, as midnight: `age(DATE &apos;2026-03-01&apos;, DATE &apos;2025-01-15&apos;)` is 13 months, 17 days |
| `TIME` values | Error: `No function matches the given name and argument types &apos;age(TIME, TIME)&apos;` |
| Two strings: `age(&apos;2026-03-01&apos;, &apos;2026-01-01&apos;)` | Error: `Could not choose a best candidate function`; cast them to `TIMESTAMP` |
| A string that isn't a timestamp, such as `&apos;abc&apos;` | Error: `invalid timestamp field format: &quot;abc&quot;, expected format is (YYYY-MM-DD HH:MM:SS[.US][&#xb1;HH:MM\| ZONE])` |
| A number, boolean, `BLOB` or list | Error: `No function matches the given name and argument types`, for example `&apos;age(TIMESTAMP, INTEGER_LITERAL)&apos;` |

The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-date-time-age#set-up-the-examples):

```
run(f"SELECT id, age(TIMESTAMP '2026-12-31 00:00:00', ts) FROM {TABLE} WHERE id = 4")
run("SELECT age(NULL::TIMESTAMP, TIMESTAMP '2026-01-01 00:00:00'), age(TIMESTAMP 'infinity', TIMESTAMP '2026-01-01 00:00:00'), "
    "age(TIMESTAMP_NS '2026-03-01 00:00:00.000000001', TIMESTAMP_NS '2026-03-01 00:00:00'), "
    "age(DATE '2026-03-01', DATE '2025-01-15')")
run("SELECT age(TIME '12:00:00', TIME '08:00:00')")
run("SELECT age('2026-03-01', '2026-01-01')")
run("SELECT age(TIMESTAMP '2026-12-31 00:00:00', 'abc')")
run("SELECT age(TIMESTAMP '2026-12-31 00:00:00', 123)")
```

```
[(4, None)]
[(None, None, MonthDayNano(months=0, days=0, nanoseconds=0), MonthDayNano(months=13, days=17, nanoseconds=0))]
InternalError: Binder Error: No function matches the given name and argument types 'age(TIME, TIME)'. You might need to add explicit type casts.
InternalError: Binder Error: Could not choose a best candidate function for the function call "age(STRING_LITERAL, STRING_LITERAL)". In order to select one, please add explicit type casts.
OSError: invalid timestamp field format: "abc", expected format is (YYYY-MM-DD HH:MM:SS[.US][±HH:MM| ZONE])
InternalError: Binder Error: No function matches the given name and argument types 'age(TIMESTAMP, INTEGER_LITERAL)'. You might need to add explicit type casts.
```

The month-end, leap-day and negative cases from [What it does](/develop/docs/dev-database-reference-sql-functions-date-time-age#what-it-does), as intervals and as text:

```
run("SELECT age(TIMESTAMP '2026-03-01 00:00:00', TIMESTAMP '2026-01-31 00:00:00'), "
    "age(TIMESTAMP '2026-02-28 00:00:00', TIMESTAMP '2026-01-31 00:00:00'), "
    "age(TIMESTAMP '2025-02-28 00:00:00', TIMESTAMP '2024-02-29 00:00:00'), "
    "age(TIMESTAMP '2028-02-29 00:00:00', TIMESTAMP '2024-02-29 00:00:00'), "
    "age(TIMESTAMP '2026-01-31 00:00:00', TIMESTAMP '2026-03-01 00:00:00')")
run("SELECT CAST(age(TIMESTAMP '2026-12-31 00:00:00', TIMESTAMP '2026-02-28 23:15:00') AS VARCHAR), "
    "CAST(age(TIMESTAMP '2026-01-31 00:00:00', TIMESTAMP '2026-03-01 00:00:00') AS VARCHAR)")
```

```
[(MonthDayNano(months=1, days=1, nanoseconds=0), MonthDayNano(months=0, days=28, nanoseconds=0), MonthDayNano(months=11, days=28, nanoseconds=0), MonthDayNano(months=48, days=0, nanoseconds=0), MonthDayNano(months=-1, days=-1, nanoseconds=0))]
[('10 months 2 days 00:45:00', '-1 month -1 day')]
```

## Examples

The time from each event to the end of 2026, from the timestamp and from the date:

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

```
[(1, MonthDayNano(months=10, days=2, nanoseconds=2700000000000), MonthDayNano(months=10, days=3, nanoseconds=0)), (2, MonthDayNano(months=9, days=29, nanoseconds=55754876544000), MonthDayNano(months=9, days=30, nanoseconds=0)), (3, MonthDayNano(months=24, days=0, nanoseconds=43200000000000), MonthDayNano(months=24, days=1, nanoseconds=0)), (4, None, None), (5, MonthDayNano(months=34, days=2, nanoseconds=0), MonthDayNano(months=34, days=2, nanoseconds=0))]
```

The one-argument form gives an interval for each row; it equals the two-argument form measured from midnight today:

```
run(f"SELECT id, typeof(age(ts)), age(ts) = age(CAST(CAST(CAST(now() AS TIMESTAMP) AS DATE) AS TIMESTAMP), ts) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")
```

```
[(1, 'INTERVAL', True), (2, 'INTERVAL', True), (3, 'INTERVAL', True), (4, 'INTERVAL', None), (5, 'INTERVAL', True)]
```

Keep only the events less than a year before the end of 2026:

```
run(f"SELECT id FROM {TABLE} WHERE age(TIMESTAMP '2026-12-31 00:00:00', ts) < INTERVAL 1 YEAR 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 `age()` is always computed by the Query Engine:

- **In the `SELECT` list,** the scan reads the column and the Query Engine computes `age()` for each row it returns.
- **In `WHERE`,** the scan sends the column for every row, and a `FILTER` step in the Query Engine computes `age()` 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 age(TIMESTAMP '2026-12-31 00:00:00', ts) < INTERVAL 1 YEAR")
```

```
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (normalized_interval(age( │
│   '2026-12-31 00:00:00':  │
│ :TIMESTAMP, ts)) < '1 year│
│        '::INTERVAL)       │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             ts            │
│             id            │
│                           │
└───────────────────────────┘
```

## Make it faster

- **Subtract the interval from the constant instead.** `ts &gt; TIMESTAMP &apos;2026-12-31 00:00:00&apos; - INTERVAL 1 YEAR` keeps the same rows as `age(TIMESTAMP &apos;2026-12-31 00:00:00&apos;, ts) &lt; INTERVAL 1 YEAR`, 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 ts > TIMESTAMP '2026-12-31 00:00:00' - INTERVAL 1 YEAR ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE ts > TIMESTAMP '2026-12-31 00:00:00' - INTERVAL 1 YEAR")
```

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

- **For "in the last N days", use the current time the same way:** `ts &gt; CAST(now() AS TIMESTAMP) - INTERVAL 7 DAY` is pushed down too ([now](/develop/docs/dev-database-reference-sql-functions-date-time-now)).

## Limitations and errors

- **Interval comparisons count a month as 30 days.** `INTERVAL 1 MONTH = INTERVAL 30 DAY` and `INTERVAL 1 YEAR = INTERVAL 360 DAY` are both true, and `INTERVAL 1 YEAR = INTERVAL 365 DAY` is false. For a cut-off on the calendar, compare the timestamps, as in [Make it faster](/develop/docs/dev-database-reference-sql-functions-date-time-age#make-it-faster).
- **No time-zone support.** On `TIMESTAMP WITH TIME ZONE` values, such as `now()`, `age` fails with an `Extension Autoloading Error`. Cast the values to `TIMESTAMP` first; see [time zones](/develop/docs/dev-database-reference-sql-functions-date-time-time-zones).
- **No `TIME` values.** For the time between two times of day, use [date_diff](/develop/docs/dev-database-reference-sql-functions-date-time-date-diff) with `&apos;minute&apos;` or `&apos;second&apos;`.

```
run("SELECT INTERVAL 1 MONTH = INTERVAL 30 DAY, INTERVAL 1 YEAR = INTERVAL 360 DAY, INTERVAL 1 YEAR = INTERVAL 365 DAY")
run("SELECT date_diff('minute', TIME '08:00:00', TIME '12:00:00')")
```

```
[(True, True, False)]
[(240,)]
```

## Similar functions

| Function | Returns | From `2026-01-31` to `2026-03-01` (`age` gives 1 month, 1 day) |
| --- | --- | --- |
| [-](/develop/docs/dev-database-reference-sql-functions-date-time-minus) | `TIMESTAMP - TIMESTAMP`: an `INTERVAL` in days and time, never months | 29 days |
| [date_sub](/develop/docs/dev-database-reference-sql-functions-date-time-date-sub) | The number of complete parts, as a `BIGINT` | `date_sub(&apos;month&apos;, ...)` is `1` |
| [date_diff](/develop/docs/dev-database-reference-sql-functions-date-time-date-diff) | The number of part boundaries crossed, as a `BIGINT` | `date_diff(&apos;month&apos;, ...)` is `2` |
| [normalized_interval](/develop/docs/dev-database-reference-sql-functions-date-time-normalized-interval) | An interval rewritten with every 30 days as a month |  |

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

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

Clean up:

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

```
[]
[]
```

## See also

- [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).
- [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.