make_timestamp

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Building values. Builds a timestamp from its parts, or from a number of microseconds since 1970-01-01 00:00:00.

Syntax

make_timestamp(year, month, day, hour, minute, seconds)
make_timestamp(microseconds)

Returns: TIMESTAMP (microsecond precision, no time zone).

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_make_timestamp. 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 timestamp (ts) and the same moment as microseconds since 1970-01-01 (us). Row 4 has no values, and row 5 is the last microsecond 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_make_timestamp"'

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, ts TIMESTAMP, us BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, TIMESTAMP '2026-02-28 23:15:00', 1772320500000000), "
    "(2, TIMESTAMP '2026-03-01 08:30:45.123456', 1772353845123456), "
    "(3, TIMESTAMP '1970-01-01 00:00:00', 0), "
    "(4, NULL, NULL), "
    "(5, TIMESTAMP '1969-12-31 23:59:59.999999', -1)")

[]
[]
[(5,)]

What it does

make_timestamp builds a timestamp in one of two ways:

• From six parts: make_timestamp(2024, 2, 29, 23, 59, 59.999999) is 2024-02-29 23:59:59.999999. The seconds are a DOUBLE; the result keeps microseconds and rounds anything finer.
• From microseconds since 1970-01-01 00:00:00: make_timestamp(1772353845123456) is 2026-03-01 08:30:45.123456, make_timestamp(0) is 1970-01-01 00:00:00 and make_timestamp(-1) is 1969-12-31 23:59:59.999999.

The date must exist: make_timestamp(2023, 2, 29, 0, 0, 0) fails with Date out of range: 2023-2-29. Two values carry over instead of failing: an hour of 24 is midnight of the next day, and seconds of 60 (or that round up to 60) are the next minute. A minute of 60 or negative seconds fail with Time out of range.

Years before 1 count back as BC: year -44 is 45 BC, and CAST(make_timestamp(-44, 3, 15, 12, 0, 0) AS VARCHAR) is '0045-03-15 (BC) 12:00:00'.

NULLs and edge cases

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

run("SELECT make_timestamp(NULL, 2, 28, 12, 30, 15.5), make_timestamp(2026, 1, 1, 0, 0, NULL), make_timestamp(NULL::BIGINT)")
run("SELECT make_timestamp(2026, 1, 1, 24, 0, 0), make_timestamp(2026, 1, 1, 0, 0, 60), "
    "make_timestamp(2026, 1, 1, 0, 0, 59.9999999), make_timestamp('1772353845123456')")
run("SELECT make_timestamp(2023, 2, 29, 0, 0, 0)")
run("SELECT make_timestamp(2026, 1, 1, 0, 60, 0)")
run("SELECT make_timestamp(2026, 1, 1, 0, 0, -1)")
run("SELECT make_timestamp(294247, 1, 10, 4, 0, 55)")
run("SELECT CAST(make_timestamp(9223372036854775807) AS VARCHAR)")
run("SELECT make_timestamp(1772353845.5)")
run("SELECT make_timestamp(2026, 1, 1, 0, 0)")

[(None, None, None)]
[(datetime.datetime(2026, 1, 2, 0, 0), datetime.datetime(2026, 1, 1, 0, 1), datetime.datetime(2026, 1, 1, 0, 1), datetime.datetime(2026, 3, 1, 8, 30, 45, 123456))]
OSError: Date out of range: 2023-2-29
OSError: Time out of range: 0:60:0.0
OSError: Time out of range: 0:0:-1.0
OSError: Date and time not in timestamp range
OSError: Timestamp microseconds out of range: 9223372036854775807
InternalError: Binder Error: No function matches the given name and argument types 'make_timestamp(DECIMAL(11,1))'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'make_timestamp(INTEGER_LITERAL, INTEGER_LITERAL, INTEGER_LITERAL, INTEGER_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

Turn each row's microseconds back into a timestamp. It matches ts:

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

[(1, datetime.datetime(2026, 2, 28, 23, 15), datetime.datetime(2026, 2, 28, 23, 15)), (2, datetime.datetime(2026, 3, 1, 8, 30, 45, 123456), datetime.datetime(2026, 3, 1, 8, 30, 45, 123456)), (3, datetime.datetime(1970, 1, 1, 0, 0), datetime.datetime(1970, 1, 1, 0, 0)), (4, None, None), (5, datetime.datetime(1969, 12, 31, 23, 59, 59, 999999), datetime.datetime(1969, 12, 31, 23, 59, 59, 999999))]

Noon on the day of each event, built from the parts of ts:

run(f"SELECT id, make_timestamp(year(ts), month(ts), day(ts), 12, 0, 0.0) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, datetime.datetime(2026, 2, 28, 12, 0)), (2, datetime.datetime(2026, 3, 1, 12, 0)), (3, datetime.datetime(1970, 1, 1, 12, 0)), (4, None), (5, datetime.datetime(1969, 12, 31, 12, 0))]

Keep the events after a moment given in microseconds:

run(f"SELECT id FROM {TABLE} WHERE ts > make_timestamp(1772300000000000) 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:

• On constants, such as make_timestamp(1772300000000000), the Query Engine computes the timestamp once, before the scan. ts > make_timestamp(1772300000000000) is then a comparison of a column with a constant, and it's pushed down to VAST DataBase: the plan has no FILTER step.
• On columns, in the SELECT list, the scan reads the columns and the Query Engine builds the timestamp for each row it returns. In WHERE, the scan sends the columns for every row, and a FILTER step in the Query Engine builds the timestamp and keeps the rows that match.

The two plans, one after the other:

plan(f"SELECT id FROM {TABLE} WHERE ts > make_timestamp(1772300000000000)")
plan(f"SELECT id FROM {TABLE} WHERE make_timestamp(us) > TIMESTAMP '2026-01-01 00:00:00'")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│   (make_timestamp(us) >   │
│   '2026-01-01 00:00:00':  │
│        :TIMESTAMP)        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             us            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Build the bound, not the column. If you store TIMESTAMP values, compare the column with make_timestamp(...) of constants, which is pushed down.
• Compare epoch numbers as numbers. If the table holds microseconds since 1970 in a BIGINT column, filter with us > 1772300000000000 rather than make_timestamp(us) > ...: a comparison of the column with a number is pushed down, and its plan has no FILTER step.

plan(f"SELECT id FROM {TABLE} WHERE us > 1772300000000000")

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

Limitations and errors

• Microseconds only. The result is a microsecond TIMESTAMP. For nanoseconds, use make_timestamp_ns.
• No time zone. There's no make_timestamptz. Build the timestamp in UTC and cast it, CAST(make_timestamp(...) AS TIMESTAMPTZ), if you need a TIMESTAMP WITH TIME ZONE; see time zones.
• Years outside 1 to 9999 can't be read as Python datetimes. The timestamp range reaches 294247-01-10 04:00:54.775806, but datetime.datetime in your client holds only years 1 to 9999. Cast such a value to VARCHAR to read it.
• Whole numbers for the microseconds form. A decimal fails with No function matches the given name and argument types. For a number of seconds with a fraction, use to_timestamp.

run("SELECT CAST(make_timestamp(2026, 3, 1, 8, 30, 0.0) AS TIMESTAMPTZ)")
run("SELECT CAST(make_timestamp(294247, 1, 10, 4, 0, 54.775806) AS VARCHAR), "
    "CAST(make_timestamp(-44, 3, 15, 12, 0, 0) AS VARCHAR)")

[(datetime.datetime(2026, 3, 1, 8, 30, tzinfo=zoneinfo.ZoneInfo(key='UTC')),)]
[('294247-01-10 04:00:54.775806', '0045-03-15 (BC) 12:00:00')]

Similar functions

run("SELECT make_timestamp(1735566330123456), CAST(to_timestamp(1735566330.123456) AS TIMESTAMP), "
    "epoch_ms(1735566330123), CAST(make_timestamp_ns(1735566330123456789) AS VARCHAR)")

[(datetime.datetime(2024, 12, 30, 13, 45, 30, 123456), datetime.datetime(2024, 12, 30, 13, 45, 30, 123456), datetime.datetime(2024, 12, 30, 13, 45, 30, 123000), '2024-12-30 13:45:30.123456789')]

Clean up:

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

[]
[]

See also

• make_date, make_time and make_timestamp_ns: build a date, a time or a nanosecond timestamp.
• epoch_us: the microseconds since 1970 of a timestamp, the reverse of make_timestamp(microseconds).
• Date and time functions: all the date and time functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.