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

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

# date_diff

*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 how many times a part, such as the month or the day, changes between two dates, timestamps or times: from 31 January to 1 February is one month, because the month changes. `datediff` is the same function. To count only complete periods, use [date_sub](/develop/docs/dev-database-reference-sql-functions-date-time-date-sub).

## Syntax

```
date_diff(part, start, end)
datediff(part, start, end)
```

| Argument | Type | Description |
| --- | --- | --- |
| `part` | `VARCHAR` | The unit to count, such as `'month'`, `'day'` or `'hour'`. Names aren't case-sensitive; the list is in [What it does](/develop/docs/dev-database-reference-sql-functions-date-time-date-diff#what-it-does). |
| `start`, `end` | Both `DATE`, both `TIMESTAMP` (also `TIMESTAMP_NS` and `TIMESTAMP_MS`), or both `TIME` | The two values. A `DATE` with a `TIMESTAMP` works: the date is read as midnight. |

**Returns:** `BIGINT`: positive when `end` is after `start`, negative when it's before.

**Aliases:** `datediff`.

## 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_date_diff`. 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`), a timestamp (`ts`) and a time of day (`t`). 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_date_diff"'

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

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

## What it does

`date_diff` counts the boundaries of `part` between `start` and `end`. From `2026-01-31` to `2026-02-01` is one month and one day; from `2025-12-31` to `2026-01-01` is one year. For timestamps the time counts too: from `23:59:59` to `00:00:00` the next day is one day, one hour and one second, and from `08:00:00` back to `07:59:59.999999` is `-1` hour.

`&apos;week&apos;` is the exception: it counts whole seven-day periods, so from a Saturday to the next day is `0` weeks, and 29 days is `4`.

These are the part names it accepts, with the result from `TIMESTAMP &apos;2024-02-29 13:45:30.123456&apos;` to `TIMESTAMP &apos;2026-03-01 08:30:45.5&apos;`:

| Part | Also accepted | Result | With `TIME` values |
| --- | --- | --- | --- |
| `&apos;year&apos;` | `&apos;y&apos;`, `&apos;yr&apos;`, `&apos;yrs&apos;`, `&apos;years&apos;`; `&apos;isoyear&apos;` | `2` | Error |
| `&apos;quarter&apos;` | `&apos;quarters&apos;` | `8` | Error |
| `&apos;month&apos;` | `&apos;mon&apos;`, `&apos;mons&apos;`, `&apos;months&apos;` | `25` | Error |
| `&apos;week&apos;` | `&apos;w&apos;`, `&apos;weeks&apos;`, `&apos;weekofyear&apos;`, `&apos;yearweek&apos;` | `104` | Error |
| `&apos;day&apos;` | `&apos;d&apos;`, `&apos;days&apos;`, `&apos;dayofmonth&apos;`, `&apos;dow&apos;`, `&apos;dayofweek&apos;`, `&apos;weekday&apos;`, `&apos;isodow&apos;`, `&apos;doy&apos;`, `&apos;dayofyear&apos;`, `&apos;julian&apos;`, `&apos;jd&apos;` | `731` | Error |
| `&apos;decade&apos;`, `&apos;century&apos;`, `&apos;millennium&apos;` | `&apos;dec&apos;`, `&apos;c&apos;`, `&apos;mil&apos;` and their plurals | `0` | Error |
| `&apos;hour&apos;` | `&apos;h&apos;`, `&apos;hr&apos;`, `&apos;hrs&apos;`, `&apos;hours&apos;` | `17539` | Works |
| `&apos;minute&apos;` | `&apos;m&apos;`, `&apos;min&apos;`, `&apos;mins&apos;`, `&apos;minutes&apos;` | `1052325` | Works |
| `&apos;second&apos;` | `&apos;s&apos;`, `&apos;sec&apos;`, `&apos;secs&apos;`, `&apos;seconds&apos;`, `&apos;epoch&apos;` | `63139515` | Works |
| `&apos;milliseconds&apos;` | `&apos;millisecond&apos;`, `&apos;ms&apos;`, `&apos;msec&apos;`, `&apos;msecs&apos;`, `&apos;msecond&apos;`, `&apos;mseconds&apos;` | `63139515377` | Works |
| `&apos;microseconds&apos;` | `&apos;microsecond&apos;`, `&apos;us&apos;`, `&apos;usec&apos;`, `&apos;usecs&apos;`, `&apos;usecond&apos;`, `&apos;useconds&apos;` | `63139515376544` | Works |

On `TIME` values, `date_diff` counts within one day: from `TIME &apos;23:00:00&apos;` to `TIME &apos;01:00:00&apos;` is `-22` hours.

**The part can come from a column**, so each row can count in a different unit.

## NULLs and edge cases

| Input | Result |
| --- | --- |
| A NULL value (row 4) | `NULL` |
| A NULL part: `date_diff(NULL, DATE &apos;2026-01-01&apos;, DATE &apos;2026-02-01&apos;)` | `NULL` |
| `DATE &apos;infinity&apos;` as either value | `NULL` |
| A `DATE` and a string: `date_diff(&apos;day&apos;, DATE &apos;2026-01-01&apos;, &apos;2026-02-01&apos;)` | `31`: the string is read as a date |
| `&apos;YEAR&apos;`, `&apos;Year&apos;` | Work: names aren't case-sensitive |
| `&apos; year&apos;`, `&apos;yyyy&apos;`, `&apos;mm&apos;`, `&apos;q&apos;`, `&apos;fortnight&apos;` | Error: `extract specifier &quot; year&quot; not recognized` (with the name you gave) |
| `&apos;nanosecond&apos;` and its other spellings | Error: `extract specifier &quot;nanosecond&quot; not recognized` |
| `&apos;era&apos;`, `&apos;timezone&apos;`, `&apos;timezone_hour&apos;`, `&apos;timezone_minute&apos;` | Error: `Specifier type not implemented for DATEDIFF` |
| `TIME` values with a date part such as `&apos;day&apos;` | Error: `&quot;time&quot; units &quot;day&quot; not recognized` |
| A `TIME` and a `TIMESTAMP` | Error: `No function matches the given name and argument types &apos;date_diff(STRING_LITERAL, TIME, TIMESTAMP)&apos;` |
| Two strings, or two untyped `NULL`s | Error: `Could not choose a best candidate function`; cast the values |
| A string that isn't a date: `date_diff(&apos;day&apos;, &apos;abc&apos;, DATE &apos;2026-12-31&apos;)` | Error: `invalid date field format: &quot;abc&quot;, expected format is (YYYY-MM-DD)` |
| A number, boolean, `BLOB`, list or `INTERVAL` | Error: `No function matches the given name and argument types`, for example `&apos;date_diff(STRING_LITERAL, INTEGER_LITERAL, DATE)&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-date-diff#set-up-the-examples):

```
run(f"SELECT id, date_diff('day', d, DATE '2026-12-31') FROM {TABLE} WHERE id = 4")
run("SELECT date_diff(NULL, DATE '2026-01-01', DATE '2026-02-01'), date_diff('day', DATE '2026-01-01', DATE 'infinity'), "
    "date_diff('day', DATE '2026-01-01', '2026-02-01'), date_diff('YEAR', DATE '2024-02-29', DATE '2026-03-01')")
run("SELECT date_diff('yyyy', DATE '2024-02-29', DATE '2026-03-01')")
run("SELECT date_diff('nanosecond', DATE '2024-02-29', DATE '2026-03-01')")
run("SELECT date_diff('era', DATE '2024-02-29', DATE '2026-03-01')")
run("SELECT date_diff('day', TIME '08:00:00', TIME '09:00:00')")
run("SELECT date_diff('hour', TIME '08:00:00', TIMESTAMP '2026-03-01 09:00:00')")
run("SELECT date_diff('day', '2026-01-01', '2026-02-01')")
run("SELECT date_diff('day', 'abc', DATE '2026-12-31')")
run("SELECT date_diff('day', 123, DATE '2026-12-31')")
```

```
[(4, None)]
[(None, None, 31, 2)]
OSError: extract specifier "yyyy" not recognized
OSError: extract specifier "nanosecond" not recognized
OSError: Specifier type not implemented for DATEDIFF
OSError: "time" units "day" not recognized
InternalError: Binder Error: No function matches the given name and argument types 'date_diff(STRING_LITERAL, TIME, TIMESTAMP)'. You might need to add explicit type casts.
InternalError: Binder Error: Could not choose a best candidate function for the function call "date_diff(STRING_LITERAL, STRING_LITERAL, STRING_LITERAL)". In order to select one, please add explicit type casts.
OSError: invalid date field format: "abc", expected format is (YYYY-MM-DD)
InternalError: Binder Error: No function matches the given name and argument types 'date_diff(STRING_LITERAL, INTEGER_LITERAL, DATE)'. You might need to add explicit type casts.
```

Boundaries, not complete periods; and `&apos;week&apos;`, which counts whole weeks:

```
run("SELECT date_diff('month', DATE '2026-01-31', DATE '2026-02-01'), date_diff('year', DATE '2025-12-31', DATE '2026-01-01'), "
    "date_diff('day', TIMESTAMP '2026-03-01 23:59:59', TIMESTAMP '2026-03-02 00:00:00'), "
    "date_diff('hour', TIMESTAMP '2026-03-01 08:00:00', TIMESTAMP '2026-03-01 07:59:59.999999')")
run("SELECT date_diff('week', DATE '2026-03-07', DATE '2026-03-08'), date_diff('week', DATE '2026-01-31', DATE '2026-03-01'), "
    "date_diff('week', DATE '2026-03-01', DATE '2026-03-08'), date_diff('hour', TIME '23:00:00', TIME '01:00:00')")
```

```
[(1, 1, 1, -1)]
[(0, 4, 1, -22)]
```

## Examples

The days from each event to the end of 2026, the hours from each timestamp to 1 March 2026, and the months to the end of 2026:

```
run(f"SELECT id, date_diff('day', d, DATE '2026-12-31'), datediff('hour', ts, TIMESTAMP '2026-03-01 00:00:00'), "
    f"date_diff('month', ts, TIMESTAMP '2026-12-31 00:00:00') FROM {TABLE} ORDER BY id LIMIT 10")
```

```
[(1, 306, 1, 10), (2, 305, -8, 9), (3, 731, 10212, 24), (4, None, None, None), (5, 1036, 17544, 34)]
```

The minutes from each time of day to noon, and the hours between each date and its timestamp:

```
run(f"SELECT id, date_diff('minute', t, TIME '12:00:00'), date_diff('hour', d, ts) FROM {TABLE} ORDER BY id LIMIT 10")
```

```
[(1, -675, 23), (2, 210, 8), (3, 0, 12), (4, None, None), (5, 720, 0)]
```

Keep only the events less than 306 days before the end of 2026:

```
run(f"SELECT id FROM {TABLE} WHERE date_diff('day', d, DATE '2026-12-31') < 306 ORDER BY id LIMIT 10")
```

```
[(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 `date_diff()` is always computed by the Query Engine:

- **In the `SELECT` list,** the scan reads the columns and the Query Engine computes `date_diff()` for each row it returns.
- **In `WHERE`,** the scan sends the columns for every row, and a `FILTER` step in the Query Engine computes `date_diff()` 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 date_diff('day', d, DATE '2026-12-31') < 306")
```

```
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (date_diff('day', d, '2026│
│   -12-31'::DATE) < 306)   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             d             │
│             id            │
│                           │
└───────────────────────────┘
```

## Make it faster

- **Compare the column with a computed constant.** `d &gt; DATE &apos;2026-12-31&apos; - 306` keeps the same rows as `date_diff(&apos;day&apos;, d, DATE &apos;2026-12-31&apos;) &lt; 306`, and it's pushed down to VAST DataBase, so rows that don't match never leave storage. The constant side is computed once. Its plan has no `FILTER` step:

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

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

- **For timestamps, subtract an interval from the constant:** `ts &gt; TIMESTAMP &apos;2026-03-01 00:00:00&apos; - INTERVAL 1 HOUR` is pushed down; see [-](/develop/docs/dev-database-reference-sql-functions-date-time-minus#make-it-faster).

## Limitations and errors

- **No time-zone support.** On `TIMESTAMP WITH TIME ZONE` values, such as `now()`, `date_diff` 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 nanoseconds.** Use `&apos;microseconds&apos;`; the fraction of a microsecond in a `TIMESTAMP_NS` value isn't counted.
- **Other SQL dialects' names aren't available.** `timestampdiff` fails with `Scalar Function with name timestampdiff does not exist!`, and part names such as `&apos;yyyy&apos;`, `&apos;dd&apos;` and `&apos;hh&apos;` aren't recognized. Use `date_diff` with the names in [What it does](/develop/docs/dev-database-reference-sql-functions-date-time-date-diff#what-it-does).

```
run("SELECT date_diff('microsecond', TIMESTAMP '2026-02-28 23:15:00', TIMESTAMP_NS '2026-02-28 23:15:00.123456789')")
run("SELECT timestampdiff(DAY, DATE '2026-03-01', DATE '2026-03-08')")
run("SELECT date_diff('day', DATE '2026-03-01', DATE '2026-03-08')")
```

```
[(123456,)]
InternalError: Catalog Error: Scalar Function with name timestampdiff does not exist!
[(7,)]
```

## Similar functions

| Function | Returns | From `DATE &apos;2026-01-31&apos;` to `DATE &apos;2026-03-01&apos;` (`date_diff(&apos;month&apos;, ...)` gives `2`) |
| --- | --- | --- |
| [date_sub](/develop/docs/dev-database-reference-sql-functions-date-time-date-sub) | The number of complete parts | `date_sub(&apos;month&apos;, ...)` is `1` |
| [age](/develop/docs/dev-database-reference-sql-functions-date-time-age) | An `INTERVAL` in months, days and time | `1 month 1 day` |
| [-](/develop/docs/dev-database-reference-sql-functions-date-time-minus) | `DATE - DATE` is the days between them; `TIMESTAMP - TIMESTAMP` an `INTERVAL` in days and time | `29` |
| [epoch](/develop/docs/dev-database-reference-sql-functions-date-time-epoch) | Seconds since 1970; subtract two for the exact seconds between them, with a fraction |  |

From `08:59` to `09:01`, `date_diff(&apos;hour&apos;, ...)` is `1` (the hour changes), `date_sub(&apos;hour&apos;, ...)` is `0`, and the difference of the epochs is `120.0` seconds.

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

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

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.