Welcome to the new DOCS.vastdata.com. We've added new menus and a version selector, plus new persona and resource pages to help customers get started. Looking for a previous article? Use the docs search.

Press CTRL+K to open search

time_bucket

Prev Next

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Truncating and bucketing. Returns the start of the fixed-width bucket a date or timestamp falls in, for example the six-hour period or the seven-day week that contains it. An origin or an offset moves where the buckets start.

Syntax

time_bucket(width, value)
time_bucket(width, value, origin)
time_bucket(width, value, offset)
Argument Type Description
width INTERVAL The width of each bucket, greater than zero. Either months and years (INTERVAL 1 MONTH, INTERVAL 10 YEAR) or days and smaller units (INTERVAL 7 DAY, INTERVAL 15 MINUTE), not both.
value DATE or TIMESTAMP (also TIMESTAMP_NS and TIMESTAMP_MS) The value to place in a bucket.
origin DATE or TIMESTAMP Optional. A moment where a bucket starts; every bucket start is a whole number of widths from it. It can be before or after value.
offset INTERVAL Optional. Moves every bucket start by this interval; it can be negative.

Returns: the type of value: DATE for a DATE, TIMESTAMP for a TIMESTAMP or another timestamp type. A DATE with a TIMESTAMP origin returns a TIMESTAMP.

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

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

time_bucket divides the time line into buckets of width and returns the start of the one that contains value. It always rounds down, also before 1970: TIMESTAMP '1969-12-31 23:59:59.999999' in one-day buckets is 1969-12-31 00:00:00.

Without an origin or an offset, the buckets are aligned like this:

Width Bucket starts Example Result
Hours, minutes, seconds Multiples of the width from midnight INTERVAL 15 MINUTE on 08:37:12; INTERVAL 6 HOUR on 2026-02-28 23:15 08:30:00; 2026-02-28 18:00
INTERVAL 1 DAY Midnight TIMESTAMP '1969-12-31 23:59:59.999999' 1969-12-31 00:00:00
INTERVAL 7 DAY, INTERVAL 1 WEEK Monday DATE '2026-03-04' 2026-03-02 (a Monday)
Months The first of a month; three-month buckets start in January, April, July and October INTERVAL 3 MONTH on 2026-05-15 2026-04-01
Years 1 January; ten-year buckets start in a year that ends in 0 INTERVAL 10 YEAR on 2026-03-15 2020-01-01

Origin. With an origin, every bucket starts a whole number of widths from it. One-day buckets with origin TIMESTAMP '2026-01-01 06:00:00' run from 06:00 to 06:00, and weekly buckets with a Sunday as origin start on Sundays. The origin can be after the value.

Offset. An offset moves every default bucket start: time_bucket(INTERVAL 1 DAY, ts, INTERVAL 6 HOUR) gives days that start at 06:00, and INTERVAL '-6 hours' days that start at 18:00 the evening before. With a month width, INTERVAL 9 DAY gives months that start on the 10th.

The width, the origin and the offset can come from columns, so each row can use a different bucket.

NULLs and edge cases

Input Result
A NULL value (row 4) NULL
A NULL width, origin or offset NULL
An untyped NULL value: time_bucket(INTERVAL 6 HOUR, NULL) NULL
INTERVAL 1 MICROSECOND The value unchanged
A DATE with a width under a day: time_bucket(INTERVAL 1 HOUR, DATE '2026-03-01') 2026-03-01, still a DATE
A TIMESTAMP_NS value A TIMESTAMP: the nanoseconds are dropped
DATE 'infinity' infinity
The width as a string: time_bucket('1 day', ts) Works: the string is read as an interval
A zero or negative width Error: Period must be greater than 0
A width with months and days: INTERVAL '1 month 1 day' Error: Month intervals cannot have day or time component
A TIME or INTERVAL value Error: No function matches the given name and argument types 'time_bucket(INTERVAL, TIME)'
A string value, such as 'abc' Error: Could not choose a best candidate function for the function call "time_bucket(INTERVAL, STRING_LITERAL)"; cast it to TIMESTAMP
A number, boolean, BLOB or list Error: No function matches the given name and argument types, for example 'time_bucket(INTERVAL, INTEGER_LITERAL)'

The same cases, as the cluster returns them. They use the table and helpers from Set up the examples:

run(f"SELECT id, time_bucket(INTERVAL 6 HOUR, ts) FROM {TABLE} WHERE id = 4")
run("SELECT time_bucket(NULL, TIMESTAMP '2026-03-01 08:30:00'), "
    "time_bucket(INTERVAL 1 DAY, TIMESTAMP '2026-03-01 08:30:00', NULL::TIMESTAMP), "
    "time_bucket(INTERVAL 1 DAY, TIMESTAMP '2026-03-01 08:30:00', NULL::INTERVAL), time_bucket(INTERVAL 6 HOUR, NULL)")
run("SELECT time_bucket(INTERVAL 1 MICROSECOND, TIMESTAMP '2026-03-01 08:37:12.123456'), "
    "time_bucket(INTERVAL 1 HOUR, DATE '2026-03-01'), "
    "CAST(time_bucket(INTERVAL 1 SECOND, TIMESTAMP_NS '2026-03-01 08:30:00.123456789') AS VARCHAR), "
    "CAST(time_bucket(INTERVAL 1 DAY, DATE 'infinity') AS VARCHAR), time_bucket('1 day', TIMESTAMP '2026-03-01 08:30:00')")
run("SELECT time_bucket(INTERVAL 0 DAY, DATE '2026-03-01')")
run("SELECT time_bucket(INTERVAL '1 month 1 day', DATE '2026-03-01')")
run("SELECT time_bucket(INTERVAL 1 HOUR, TIME '08:30:00')")
run("SELECT time_bucket(INTERVAL 6 HOUR, 'abc')")
run("SELECT time_bucket(INTERVAL 6 HOUR, 123)")

[(4, None)]
[(None, None, None, None)]
[(datetime.datetime(2026, 3, 1, 8, 37, 12, 123456), datetime.date(2026, 3, 1), '2026-03-01 08:30:00', 'infinity', datetime.datetime(2026, 3, 1, 0, 0))]
OSError: Period must be greater than 0
OSError: Month intervals cannot have day or time component
InternalError: Binder Error: No function matches the given name and argument types 'time_bucket(INTERVAL, TIME)'. You might need to add explicit type casts.
InternalError: Binder Error: Could not choose a best candidate function for the function call "time_bucket(INTERVAL, STRING_LITERAL)". In order to select one, please add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'time_bucket(INTERVAL, INTEGER_LITERAL)'. You might need to add explicit type casts.

The default alignment, an origin and an offset, before and after 1970:

run("SELECT time_bucket(INTERVAL 15 MINUTE, TIMESTAMP '2026-03-01 08:37:12'), "
    "time_bucket(INTERVAL 7 DAY, DATE '2026-03-04'), dayname(time_bucket(INTERVAL 7 DAY, DATE '2026-03-04')), "
    "time_bucket(INTERVAL 3 MONTH, TIMESTAMP '2026-05-15 08:30:00'), time_bucket(INTERVAL 10 YEAR, DATE '2026-03-15')")
run("SELECT time_bucket(INTERVAL 1 DAY, TIMESTAMP '2026-03-01 08:30:00', TIMESTAMP '2026-01-01 06:00:00'), "
    "time_bucket(INTERVAL 1 WEEK, DATE '2026-03-04', DATE '2026-03-01'), "
    "time_bucket(INTERVAL 1 DAY, TIMESTAMP '2026-03-01 08:30:00', INTERVAL '-6 hours'), "
    "time_bucket(INTERVAL 1 MONTH, DATE '2026-03-15', INTERVAL 9 DAY)")
run("SELECT time_bucket(INTERVAL 1 DAY, TIMESTAMP '1969-12-31 23:59:59.999999'), time_bucket(INTERVAL 7 DAY, DATE '1969-12-31')")

[(datetime.datetime(2026, 3, 1, 8, 30), datetime.date(2026, 3, 2), 'Monday', datetime.datetime(2026, 4, 1, 0, 0), datetime.date(2020, 1, 1))]
[(datetime.datetime(2026, 3, 1, 6, 0), datetime.date(2026, 3, 1), datetime.datetime(2026, 2, 28, 18, 0), datetime.date(2026, 3, 10))]
[(datetime.datetime(1969, 12, 31, 0, 0), datetime.date(1969, 12, 29))]

Examples

Each event in six-hour buckets, in weeks and in months:

run(f"SELECT id, time_bucket(INTERVAL 6 HOUR, ts), time_bucket(INTERVAL 7 DAY, d), time_bucket(INTERVAL 1 MONTH, d) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

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

Days that start at 06:00, written with an origin and with an offset; both give the same buckets:

run(f"SELECT id, time_bucket(INTERVAL 1 DAY, ts, TIMESTAMP '2026-01-01 06:00:00'), "
    f"time_bucket(INTERVAL 1 DAY, ts, INTERVAL 6 HOUR) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, datetime.datetime(2026, 2, 28, 6, 0), datetime.datetime(2026, 2, 28, 6, 0)), (2, datetime.datetime(2026, 3, 1, 6, 0), datetime.datetime(2026, 3, 1, 6, 0)), (3, datetime.datetime(2024, 12, 30, 6, 0), datetime.datetime(2024, 12, 30, 6, 0)), (4, None, None), (5, datetime.datetime(2024, 2, 28, 6, 0), datetime.datetime(2024, 2, 28, 6, 0))]

A bucket width that comes from a column: here, id days:

run(f"SELECT id, time_bucket(to_days(CAST(id AS INTEGER)), d) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, datetime.date(2026, 2, 28)), (2, datetime.date(2026, 3, 1)), (3, datetime.date(2024, 12, 28)), (4, None), (5, datetime.date(2024, 2, 26))]

Keep only the events in the bucket that starts on 1 March 2026:

run(f"SELECT id FROM {TABLE} WHERE time_bucket(INTERVAL 1 DAY, ts) = TIMESTAMP '2026-03-01 00:00:00' 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 time_bucket() is always computed by the Query Engine:

  • In the SELECT list, the scan reads the column and the Query Engine computes time_bucket() for each row it returns.
  • In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes time_bucket() 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 time_bucket(INTERVAL 1 DAY, ts) = TIMESTAMP '2026-03-01 00:00:00'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│   (time_bucket('1 day':   │
│ :INTERVAL, ts) = '2026-03 │
│ -01 00:00:00'::TIMESTAMP) │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             ts            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

  • To select one bucket, filter on a range of the raw column. ts >= TIMESTAMP '2026-03-01 00:00:00' AND ts < TIMESTAMP '2026-03-02 00:00:00' keeps the same rows as time_bucket(INTERVAL 1 DAY, ts) = TIMESTAMP '2026-03-01 00:00:00', 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-03-01 00:00:00' AND ts < TIMESTAMP '2026-03-02 00:00:00' "
    f"ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE ts >= TIMESTAMP '2026-03-01 00:00:00' AND ts < TIMESTAMP '2026-03-02 00:00:00'")

[(2,)]
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
  • Store the bucket if you filter on it often. Compute it when you write the rows and keep it in its own column: day_bucket = TIMESTAMP '2026-03-01 00:00:00' is pushed down, time_bucket(INTERVAL 1 DAY, ts) = ... isn't.

Limitations and errors

  • No GROUP BY. Grouping by a bucket, as in GROUP BY time_bucket(...), fails with The operator 'HASH_GROUP_BY' type=9 is currently not supported. Return the bucket for each row and aggregate in your client, or count one bucket at a time with a range filter, as in Make it faster.
  • No time-zone support. On a TIMESTAMP WITH TIME ZONE value, time_bucket fails with an Extension Autoloading Error, and the form that takes a time-zone name as its third argument isn't available. Cast the value to TIMESTAMP first; see time zones.
  • Months and days don't mix in one width. Use INTERVAL 1 MONTH with an offset such as INTERVAL 9 DAY instead of INTERVAL '1 month 9 days'.
  • No TIME values. time_bucket takes a DATE or a timestamp, not a TIME.
run(f"SELECT time_bucket(INTERVAL 1 MONTH, d) AS m, count(*) FROM {TABLE} GROUP BY m ORDER BY m LIMIT 10")
run("SELECT time_bucket(INTERVAL 1 DAY, TIMESTAMP '2026-03-01 08:30:00', 'UTC')")

InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.
InternalError: Binder Error: Could not choose a best candidate function for the function call "time_bucket(INTERVAL, TIMESTAMP, STRING_LITERAL)". In order to select one, please add explicit type casts.

Similar functions

Function Returns Result for TIMESTAMP '2024-12-30 13:45:30.123456'
time_bucket(INTERVAL 1 MONTH, ...) The start of the bucket, as a TIMESTAMP 2024-12-01 00:00:00
date_trunc ('month', ...) The start of the calendar part; a DATE for 'day' and coarser parts 2024-12-01
time_bucket(INTERVAL 1 HOUR, ...) Same as date_trunc('hour', ...) 2024-12-30 13:00:00
time_bucket(INTERVAL 6 HOUR, ...) Buckets date_trunc has no part for 2024-12-30 12:00:00
strftime The value as text, in a format you choose strftime(..., '%Y-%m-01') is '2024-12-01'

date_trunc works only with calendar parts (one month, one hour); time_bucket takes any width, such as six hours or ten years, and an origin.

run("SELECT time_bucket(INTERVAL 1 MONTH, TIMESTAMP '2024-12-30 13:45:30.123456'), "
    "date_trunc('month', TIMESTAMP '2024-12-30 13:45:30.123456'), "
    "time_bucket(INTERVAL 1 HOUR, TIMESTAMP '2024-12-30 13:45:30.123456'), "
    "date_trunc('hour', 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')")

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

Clean up:

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

[]
[]

See also

Related articles
  • + (add a date or interval)
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > Date and time functions
  • hour
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > Date and time functions
  • day
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > Date and time functions