date_trunc

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Truncating and bucketing. Rounds a date, timestamp or interval down to the start of the unit you name: its year, quarter, month, week, day, hour, minute, second, millisecond or microsecond. datetrunc is the same function.

Syntax

date_trunc(part, value)
datetrunc(part, value)

Returns: DATE for 'day' and coarser parts, TIMESTAMP for 'hour' and finer parts, whether value is a DATE or a timestamp. INTERVAL for an INTERVAL. When part comes from a column rather than a constant, the result is always a TIMESTAMP.

Aliases: datetrunc.

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_trunc. 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 timestamp with nanoseconds (tns) and a part name (part). 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_trunc"'

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

[]
[]
[(5,)]

What it does

date_trunc sets every field finer than part to its lowest value: date_trunc('month', TIMESTAMP '2024-02-29 13:45:30.123456') is 2024-02-01, and date_trunc('hour', ...) of the same timestamp is 2024-02-29 13:00:00. A week starts on Monday: date_trunc('week', DATE '2026-03-01'), a Sunday, is 2026-02-23.

Before 1970 it still rounds down: date_trunc('second', TIMESTAMP '1969-12-31 23:59:59.999999') is 1969-12-31 23:59:59.

On an INTERVAL, date_trunc drops the fields finer than part, and works toward zero for a negative interval: date_trunc('month', INTERVAL '1 year 2 months 3 days') is 1 year 2 months, and date_trunc('hour', INTERVAL '-90 minutes') is minus one hour. It doesn't carry days into months: date_trunc('day', INTERVAL '40 days 30 hours') is 40 days.

Parts

Part names aren't case-sensitive ('YEAR' works), but spaces aren't trimmed (' year' fails). 'nanosecond', 'era' and the time-zone parts aren't available; see Limitations and errors.

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_trunc('month', ts) FROM {TABLE} WHERE id = 4")
run("SELECT date_trunc('month', NULL::DATE), date_trunc(NULL, TIMESTAMP '2024-02-29 13:45:30'), "
    "CAST(date_trunc('month', DATE 'infinity') AS VARCHAR), CAST(date_trunc('hour', TIMESTAMP '-infinity') AS VARCHAR)")
run("SELECT date_trunc('week', DATE '2026-03-02'), date_trunc('millisecond', TIMESTAMP '1969-12-31 23:59:59.999999'), "
    "CAST(date_trunc('microsecond', TIMESTAMP_NS '2024-02-29 13:45:30.123456789') AS VARCHAR)")
run("SELECT CAST(date_trunc('year', INTERVAL '30 months') AS VARCHAR), CAST(date_trunc('week', INTERVAL '10 days') AS VARCHAR)")
run("SELECT date_trunc('month', NULL)")
run("SELECT date_trunc('fortnight', TIMESTAMP '2024-02-29 13:45:30')")
run("SELECT date_trunc('hour', TIME '13:45:30')")
run("SELECT date_trunc('month', '2024-02-29')")

[(4, None)]
[(None, None, 'infinity', '-infinity')]
[(datetime.date(2026, 3, 2), datetime.datetime(1969, 12, 31, 23, 59, 59, 999000), '2024-02-29 13:45:30.123456')]
[('2 years', '7 days')]
InternalError: Binder Error: Could not choose a best candidate function for the function call "date_trunc(STRING_LITERAL, "NULL")". In order to select one, please add explicit type casts.
InternalError: Conversion Error: extract specifier "fortnight" not recognized
InternalError: Binder Error: No function matches the given name and argument types 'date_trunc(STRING_LITERAL, TIME)'. You might need to add explicit type casts.
InternalError: Binder Error: Could not choose a best candidate function for the function call "date_trunc(STRING_LITERAL, STRING_LITERAL)". In order to select one, please add explicit type casts.

Examples

The month of each event's timestamp, the week of its date, and the hour of its date. 'hour' returns a TIMESTAMP even for a DATE:

run(f"SELECT id, date_trunc('month', ts), date_trunc('week', d), date_trunc('hour', d) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, datetime.date(2026, 2, 1), datetime.date(2026, 2, 23), datetime.datetime(2026, 2, 28, 0, 0)), (2, datetime.date(2026, 3, 1), datetime.date(2026, 2, 23), datetime.datetime(2026, 3, 1, 0, 0)), (3, datetime.date(2024, 12, 1), datetime.date(2024, 12, 30), datetime.datetime(2024, 12, 30, 0, 0)), (4, None, None, None), (5, datetime.date(1969, 12, 1), datetime.date(1969, 12, 29), datetime.datetime(1969, 12, 31, 0, 0))]

The same timestamp rounded down to each of several parts:

run("SELECT date_trunc('year', TIMESTAMP '2024-02-29 13:45:30.123456'), "
    "date_trunc('quarter', TIMESTAMP '2024-02-29 13:45:30.123456'), "
    "date_trunc('week', TIMESTAMP '2024-02-29 13:45:30.123456'), "
    "date_trunc('minute', TIMESTAMP '2024-02-29 13:45:30.123456'), "
    "date_trunc('millisecond', TIMESTAMP '2024-02-29 13:45:30.123456')")

[(datetime.date(2024, 1, 1), datetime.date(2024, 1, 1), datetime.date(2024, 2, 26), datetime.datetime(2024, 2, 29, 13, 45), datetime.datetime(2024, 2, 29, 13, 45, 30, 123000))]

Take the part from a column. Each row is rounded to its own part, and every result is a TIMESTAMP:

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

[(1, 'year', datetime.datetime(2026, 1, 1, 0, 0)), (2, 'month', datetime.datetime(2026, 3, 1, 0, 0)), (3, 'week', datetime.datetime(2024, 12, 30, 0, 0)), (4, None, None), (5, 'hour', datetime.datetime(1969, 12, 31, 23, 0))]

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

run(f"SELECT id FROM {TABLE} WHERE date_trunc('month', ts) = TIMESTAMP '2026-02-01 00:00:00' ORDER BY id LIMIT 10")

[(1,)]

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_trunc() of a column is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes date_trunc() 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_trunc() 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 date_trunc('month', ts) = TIMESTAMP '2026-02-01 00:00:00'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (date_trunc('month', ts) =│
│     '2026-02-01'::DATE)   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             ts            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

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

plan(f"SELECT id FROM {TABLE} WHERE ts >= TIMESTAMP '2026-02-01 00:00:00' AND ts < TIMESTAMP '2026-03-01 00:00:00'")
run(f"SELECT id FROM {TABLE} WHERE ts >= TIMESTAMP '2026-02-01 00:00:00' AND ts < TIMESTAMP '2026-03-01 00:00:00' "
    "ORDER BY id LIMIT 10")

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

• Apply date_trunc to a constant, not to the column. ts >= date_trunc('month', TIMESTAMP '2026-03-15 00:00:00') (everything from the start of March 2026) is computed once, and the comparison is pushed down:

plan(f"SELECT id FROM {TABLE} WHERE ts >= date_trunc('month', TIMESTAMP '2026-03-15 00:00:00')")

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

• Use date_trunc() in the SELECT list, where it doesn't stop a filter on the raw column from being pushed down.

Limitations and errors

• No time-zone support. On a TIMESTAMP WITH TIME ZONE value, such as now(), date_trunc fails with an Extension Autoloading Error. Cast the value to TIMESTAMP first: date_trunc('month', CAST(now() AS TIMESTAMP)). See time zones.
• Not for TIME. date_trunc('hour', TIME ...) fails with No function matches the given name and argument types 'date_trunc(STRING_LITERAL, TIME)'.
• No nanoseconds. 'nanosecond' (and 'ns', 'nsec' and the other spellings) fails with extract specifier "nanosecond" not recognized. On a TIMESTAMP_NS, the result is a TIMESTAMP, which holds microseconds: row 5's 1969-12-31 23:59:59.999999999 gives 1970-01-01 00:00:00 with 'second', while row 5's ts (1969-12-31 23:59:59.999999) gives 1969-12-31 23:59:59.
• No 'era' or time-zone parts. 'era', 'timezone', 'timezone_hour' and 'timezone_minute' fail with Not implemented Error: Specifier type not implemented for DATETRUNC statistics.
• Other SQL dialects' forms aren't available. Format-style parts such as 'yyyy', 'mm', 'dd', 'hh', 'mi', 'ss' and 'q' aren't recognized, and trunc(DATE, 'MM') fails with No function matches the given name and argument types. Use the part names in Parts.

run(f"SELECT id, CAST(date_trunc('second', tns) AS VARCHAR), typeof(date_trunc('second', tns)), "
    f"CAST(date_trunc('second', ts) AS VARCHAR) FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT date_trunc('nanosecond', TIMESTAMP '2024-02-29 13:45:30')")
run("SELECT date_trunc('era', TIMESTAMP '2024-02-29 13:45:30')")
run("SELECT date_trunc('mm', TIMESTAMP '2024-02-29 13:45:30')")
run("SELECT date_trunc('month', CAST(TIMESTAMPTZ '2024-02-29 13:45:30+02' AS TIMESTAMP))")

[(1, '2026-02-28 23:15:00', 'TIMESTAMP', '2026-02-28 23:15:00'), (2, '2026-03-01 08:30:45', 'TIMESTAMP', '2026-03-01 08:30:45'), (3, '2024-12-30 12:00:00', 'TIMESTAMP', '2024-12-30 12:00:00'), (4, None, 'TIMESTAMP', None), (5, '1970-01-01 00:00:00', 'TIMESTAMP', '1969-12-31 23:59:59')]
InternalError: Conversion Error: extract specifier "nanosecond" not recognized
InternalError: Not implemented Error: Specifier type not implemented for DATETRUNC statistics
InternalError: Conversion Error: extract specifier "mm" not recognized
[(datetime.date(2024, 2, 1),)]

Similar functions

run("SELECT date_trunc('month', TIMESTAMP '2024-12-30 13:45:30.123456'), datetrunc('month', TIMESTAMP '2024-12-30 13:45:30.123456'), "
    "time_bucket(INTERVAL 1 MONTH, TIMESTAMP '2024-12-30 13:45:30.123456'), "
    "time_bucket(INTERVAL 6 HOUR, TIMESTAMP '2024-12-30 13:45:30.123456'), "
    "strftime(TIMESTAMP '2024-12-30 13:45:30.123456', '%Y-%m-01'), last_day(TIMESTAMP '2024-12-30 13:45:30.123456')")

[(datetime.date(2024, 12, 1), datetime.date(2024, 12, 1), datetime.datetime(2024, 12, 1, 0, 0), datetime.datetime(2024, 12, 30, 12, 0), '2024-12-01', datetime.date(2024, 12, 31))]

Clean up:

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

[]
[]

See also

• time_bucket: buckets of any width.
• date_part: the part names, and getting one part as a number.
• Date and time functions: all the date and time functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.