date_part

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Parts of a date or time. Returns the named part of a date, time, timestamp or interval, such as its year, hour or epoch, or several parts at once as a STRUCT. datepart is the same function, and extract(part FROM value) is the standard SQL way to write it.

Syntax

date_part(part, value)
date_part([part, ...], value)
datepart(part, value)
datepart([part, ...], value)
extract(part FROM value)

Returns: BIGINT, or DOUBLE for 'epoch' and 'julian'. With a list of parts, a STRUCT with one field per part, named as you wrote it, which Python prints as a dict.

Aliases: datepart. extract(part FROM value) is the same call as date_part('part', value).

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_date_part. 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_date_part"'

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

date_part takes one part out of a value. These are the part names it accepts, with the result for TIMESTAMP '2024-02-29 13:45:30.123456' (a Thursday). Every part also works on a DATE, where the time parts are 0:

'second' is the whole second. 'milliseconds' and 'microseconds' include the seconds: for 13:45:30.987654, 'second' is 30 and 'milliseconds' is 30987. On a TIME, 'epoch' is the seconds since midnight; on an INTERVAL, its total length in seconds.

On an INTERVAL, the parts are its fields as written, not normalised: for INTERVAL '1 year 2 months 3 days 04:05:06.789', 'year' is 1, 'month' is 2, 'day' is 3, 'hour' is 4 and 'milliseconds' is 6789. A negative interval gives negative fields.

Several parts at once. Pass a list of names to get a STRUCT with one field per name: date_part(['year', 'month', 'day'], DATE '2024-02-29') returns {'year': 2024, 'month': 2, 'day': 29}, the same values as year(), month() and day().

The part can come from a column, so each row can ask for a different part.

There's no nanoseconds part: 'nanoseconds', 'ns' and the other spellings fail. Use nanosecond.

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, date_part('hour', ts) FROM {TABLE} WHERE id = 4")
run("SELECT date_part(NULL, TIMESTAMP '2024-02-29 13:45:30'), date_part('hour', DATE 'infinity'), "
    "date_part('epoch', DATE 'infinity'), date_part('YEAR', DATE '2024-02-29'), date_part('Year', DATE '2024-02-29')")
run("SELECT date_part(' year', TIMESTAMP '2024-02-29 13:45:30')")
run("SELECT date_part('yyyy', TIMESTAMP '2024-02-29 13:45:30')")
run("SELECT date_part(['year', 'year'], DATE '2024-02-29')")
run("SELECT date_part([], DATE '2024-02-29')")
run("SELECT date_part(['year', NULL], DATE '2024-02-29')")
run("SELECT date_part('year', TIME '13:45:30')")
run("SELECT date_part('week', INTERVAL '1 year 2 months 3 days')")
run("SELECT date_part('hour', NULL)")
run("SELECT date_part('year', '2024-02-29')")
run("SELECT date_part('year', '2024-02-29'::DATE)")
run("SELECT date_part('year', 2024)")

[(4, None)]
[(None, None, None, 2024, 2024)]
InternalError: Conversion Error: extract specifier " year" not recognized
InternalError: Conversion Error: extract specifier "yyyy" not recognized
InternalError: Binder Error: Duplicate struct entry name "year" in date_part
InternalError: Binder Error: date_part requires non-empty lists of part names
InternalError: Binder Error: NULL struct entry name in date_part
InternalError: Binder Error: No function matches the given name and argument types 'year(TIME)'. You might need to add explicit type casts.
OSError: interval units "week" not recognized
InternalError: Binder Error: Could not choose a best candidate function for the function call "date_part(STRING_LITERAL, "NULL")". In order to select one, please add explicit type casts.
InternalError: Binder Error: Could not choose a best candidate function for the function call "date_part(STRING_LITERAL, STRING_LITERAL)". In order to select one, please add explicit type casts.
[(2024,)]
InternalError: Binder Error: No function matches the given name and argument types 'date_part(STRING_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.

The parts of an interval, of a negative interval and of a time:

run("SELECT date_part(['year', 'month', 'day', 'hour', 'minute', 'second', 'milliseconds'], "
    "INTERVAL '1 year 2 months 3 days 04:05:06.789')")
run("SELECT date_part(['year', 'month', 'day', 'hour'], INTERVAL '-1 year -2 months -3 days -04:05:06.789')")
run("SELECT date_part('epoch', INTERVAL '1 year 2 months 3 days 04:05:06.789'), date_part('epoch', TIME '13:45:30.123456'), "
    "date_part('second', TIMESTAMP '2024-02-29 13:45:30.987654'), date_part('milliseconds', TIMESTAMP '2024-02-29 13:45:30.987654')")

[({'year': 1, 'month': 2, 'day': 3, 'hour': 4, 'minute': 5, 'second': 6, 'milliseconds': 6789},)]
[({'year': -1, 'month': -2, 'day': -3, 'hour': -4},)]
[(37015506.789, 49530.123456, 30, 30987)]

Examples

The year, month, day and hour of each event, written the three ways:

run(f"SELECT id, date_part('year', ts), datepart('month', ts), extract(day FROM ts), extract(hour FROM ts) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 2026, 2, 28, 23), (2, 2026, 3, 1, 8), (3, 2024, 12, 30, 12), (4, None, None, None, None), (5, 1969, 12, 31, 23)]

The same parts in one call, as a STRUCT, and the ISO year, week and day of the week of each date:

run(f"SELECT id, date_part(['year', 'month', 'day', 'hour'], ts) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, date_part(['isoyear', 'week', 'isodow'], d) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, {'year': 2026, 'month': 2, 'day': 28, 'hour': 23}), (2, {'year': 2026, 'month': 3, 'day': 1, 'hour': 8}), (3, {'year': 2024, 'month': 12, 'day': 30, 'hour': 12}), (4, None), (5, {'year': 1969, 'month': 12, 'day': 31, 'hour': 23})]
[(1, {'isoyear': 2026, 'week': 9, 'isodow': 6}), (2, {'isoyear': 2026, 'week': 9, 'isodow': 7}), (3, {'isoyear': 2025, 'week': 1, 'isodow': 1}), (4, None), (5, {'isoyear': 1970, 'week': 1, 'isodow': 3})]

Seconds since 1970 for each timestamp, and seconds since midnight for each time:

run(f"SELECT id, extract(epoch FROM ts), date_part('epoch', t) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 1772320500.0, 83700.0), (2, 1772353845.123456, 30645.123456), (3, 1735560000.0, 43200.0), (4, None, None), (5, -1e-06, 86399.999999)]

Keep only the events from noon onwards:

run(f"SELECT id FROM {TABLE} WHERE date_part('hour', ts) >= 12 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 date_part() is always computed by the Query Engine:

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

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

plan(f"SELECT id FROM {TABLE} WHERE date_part('hour', ts) >= 12")

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

Make it faster

• Filter on a range of the raw column, not on its year or month. ts >= TIMESTAMP '2026-01-01 00:00:00' AND ts < TIMESTAMP '2027-01-01 00:00:00' is pushed down to VAST DataBase, so rows that don't match never leave storage; date_part('year', ts) = 2026 isn't. The plan for the range has no FILTER step:

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

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

• On a TIME column, compare the column itself. t >= TIME '12:00:00' is pushed down; date_part('hour', t) >= 12 isn't.
• Store a part you filter on often. Compute it when you write the rows and keep it in its own column: event_hour >= 12 is pushed down, date_part('hour', ts) >= 12 isn't.
• Take several parts in one call. date_part(['year', 'month', 'day'], ts) returns them together as a STRUCT.

Limitations and errors

• No time-zone support. On a TIMESTAMP WITH TIME ZONE value, such as now(), date_part fails with an Extension Autoloading Error. Cast the value to TIMESTAMP first; see time zones.
• No nanoseconds part. Use nanosecond.
• TIMESTAMP_NS just before 1970. Row 5's tns, 1969-12-31 23:59:59.999999999, gives minute 0 and year 1970, not 59 and 1969. Store such values as TIMESTAMP if you need their parts; row 5's ts gives 1969.
• In extract, write decade, not dec. extract(dec FROM ts) is a syntax error; date_part('dec', ts) works.
• Unknown part names fail. Other SQL dialects' names such as 'yyyy', 'mm', 'dd', 'hh', 'mi', 'ss' and 'q' fail with extract specifier ... not recognized. Use the names in What it does.

run(f"SELECT id, date_part('minute', tns), date_part('year', tns), date_part('year', ts) FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT extract(year FROM CAST(now() AS TIMESTAMP)) >= 2026")

[(1, 15, 2026, 2026), (2, 30, 2026, 2026), (3, 0, 2024, 2024), (4, None, None, None), (5, 0, 1970, 1969)]
[(True,)]

Similar functions

run("SELECT date_part('month', DATE '2024-02-29'), month(DATE '2024-02-29'), date_trunc('month', DATE '2024-02-29'), "
    "strftime(DATE '2024-02-29', '%m'), monthname(DATE '2024-02-29')")
run("SELECT date_part(['year', 'month', 'day'], DATE '2024-02-29'), year(DATE '2024-02-29'), month(DATE '2024-02-29'), "
    "day(DATE '2024-02-29')")

[(2, 2, datetime.date(2024, 2, 1), '02', 'February')]
[({'year': 2024, 'month': 2, 'day': 29}, 2024, 2, 29)]

Clean up:

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

[]
[]

See also

• Date and time functions: all the date and time functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.