make_timestamp_ns

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Building values. Builds a nanosecond timestamp (TIMESTAMP_NS) from a number of nanoseconds since 1970-01-01 00:00:00.

Syntax

make_timestamp_ns(nanoseconds)

Returns: TIMESTAMP_NS.

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

Python's datetime holds only microseconds, so your client can't convert a TIMESTAMP_NS value that has nanoseconds. The examples read such values as text (CAST(... AS VARCHAR)) or as numbers (epoch_ns), never directly.

This block connects, defines the run() and plan() helpers, and creates a table of five ticks, each with a time in nanoseconds since 1970-01-01 (ns). Row 4 has no values, and row 5 is the last nanosecond 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_ns"'

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}.ticks"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, ns BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 1772353845123456789), "
    "(2, 1772353845123456000), "
    "(3, 0), "
    "(4, NULL), "
    "(5, -1)")

[]
[]
[(5,)]

What it does

make_timestamp_ns(1772353845123456789) is 2026-03-01 08:30:45.123456789, a TIMESTAMP_NS: every nanosecond is kept. make_timestamp_ns(0) is 1970-01-01 00:00:00, and make_timestamp_ns(-1) is 1969-12-31 23:59:59.999999999.

It's the reverse of epoch_ns: epoch_ns(make_timestamp_ns(n)) is n. nanosecond reads the seconds of the minute in nanoseconds: 45123456789 for the value above.

As text, a value whose nanoseconds are a whole number of microseconds prints with six digits or fewer: make_timestamp_ns(1772353845123456000) is '2026-03-01 08:30:45.123456'. Your client can read such a value directly, as a Python datetime.

NULLs and edge cases

The same cases, as the cluster returns them, with the values from What it does. They use the helpers from Set up the examples:

run("SELECT CAST(make_timestamp_ns(1772353845123456789) AS VARCHAR), typeof(make_timestamp_ns(1772353845123456789)), "
    "epoch_ns(make_timestamp_ns(1772353845123456789)), nanosecond(make_timestamp_ns(1772353845123456789))")
run("SELECT make_timestamp_ns(1772353845123456000), CAST(make_timestamp_ns(1772353845123456000) AS VARCHAR)")
run("SELECT make_timestamp_ns(NULL), CAST(make_timestamp_ns(0) AS VARCHAR), CAST(make_timestamp_ns(-1) AS VARCHAR)")
run("SELECT CAST(make_timestamp_ns(9223372036854775807) AS VARCHAR)")
run("SELECT CAST(make_timestamp_ns(1.5) AS VARCHAR)")
run("SELECT CAST(make_timestamp_ns('abc') AS VARCHAR)")
run("SELECT CAST(make_timestamp_ns(DATE '2026-01-01') AS VARCHAR)")
run("SELECT make_timestamp_ns(2026, 1, 1, 0, 0, 0)")

[('2026-03-01 08:30:45.123456789', 'TIMESTAMP_NS', 1772353845123456789, 45123456789)]
[(datetime.datetime(2026, 3, 1, 8, 30, 45, 123456), '2026-03-01 08:30:45.123456')]
[(None, '1970-01-01 00:00:00', '1969-12-31 23:59:59.999999999')]
OSError: Timestamp microseconds out of range: 9223372036854775807
InternalError: Binder Error: No function matches the given name and argument types 'make_timestamp_ns(DECIMAL(2,1))'. You might need to add explicit type casts.
OSError: Could not convert string 'abc' to INT64
InternalError: Binder Error: No function matches the given name and argument types 'make_timestamp_ns(DATE)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'make_timestamp_ns(INTEGER_LITERAL, INTEGER_LITERAL, INTEGER_LITERAL, INTEGER_LITERAL, INTEGER_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

Each tick as a nanosecond timestamp, read as text, and its nanoseconds read back with epoch_ns:

run(f"SELECT id, CAST(make_timestamp_ns(ns) AS VARCHAR), epoch_ns(make_timestamp_ns(ns)) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '2026-03-01 08:30:45.123456789', 1772353845123456789), (2, '2026-03-01 08:30:45.123456', 1772353845123456000), (3, '1970-01-01 00:00:00', 0), (4, None, None), (5, '1969-12-31 23:59:59.999999999', -1)]

Keep the ticks that have a time:

run(f"SELECT id FROM {TABLE} WHERE make_timestamp_ns(ns) IS NOT NULL ORDER BY id LIMIT 10")

[(1,), (2,), (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 make_timestamp_ns() on a column is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine builds the timestamp for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine builds the timestamp 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 make_timestamp_ns(ns) IS NOT NULL")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (make_timestamp_ns(ns) IS │
│          NOT NULL)        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             ns            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Compare the nanoseconds as numbers. If the table holds nanoseconds since 1970 in a BIGINT column, filter with ns > 1772300000000000000 rather than on make_timestamp_ns(ns): a comparison of the column with a number is pushed down to VAST DataBase, so rows that don't match never leave storage. Its plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE ns > 1772300000000000000")

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

• Build the timestamp in the SELECT list, where it doesn't stop a filter from being pushed down.

Limitations and errors

• Your client can't read nanoseconds directly. A TIMESTAMP_NS value with nanoseconds can't be converted to a Python datetime. Cast it to VARCHAR, or read the number with epoch_ns.
• Text with zero microseconds. When a value's microsecond digits are all zero but its nanoseconds aren't, such as 2026-02-28 23:15:00.000000789, CAST(... AS VARCHAR) puts NUL characters (\x00) in place of the six zero digits. Read such values with epoch_ns() instead.
• The largest and smallest BIGINT values fail with Timestamp microseconds out of range (the message says microseconds, though the argument is in nanoseconds).
• No parts form. To build a nanosecond timestamp from its parts, build the nanoseconds: epoch_ns(make_timestamp(...)) + n.

run("SELECT CAST(make_timestamp_ns(1772320500000000789) AS VARCHAR), epoch_ns(make_timestamp_ns(1772320500000000789))")
run("SELECT CAST(make_timestamp_ns(epoch_ns(make_timestamp(2026, 3, 1, 8, 30, 45.123456)) + 789) AS VARCHAR)")

[('2026-02-28 23:15:00.\x00\x00\x00\x00\x00\x00789', 1772320500000000789)]
[('2026-03-01 08:30:45.123456789',)]

Similar functions

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

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

Clean up:

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

[]
[]

See also

• make_timestamp: build a microsecond timestamp from parts or from microseconds.
• nanosecond and epoch_ns: read the nanoseconds of a timestamp.
• Date and time functions: all the date and time functions.
• Where filters run and pushdowns.