nanosecond

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Parts of a date or time. Returns the seconds of a timestamp or time of day, including their fraction, as a whole number of nanoseconds from 0 to 59999999999. On an interval, it returns the seconds within the minute of its time part, in nanoseconds.

Syntax

nanosecond(value)

Returns: BIGINT.

date_part and extract have no nanosecond part: use nanosecond.

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

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

nanosecond returns the seconds part of a timestamp or time of day including the whole seconds, in nanoseconds: nanosecond(TIMESTAMP_NS '2024-02-29 13:45:30.123456789') is 30123456789 (30 seconds and 123456789 nanoseconds). It's the only part function that keeps the nanoseconds of a TIMESTAMP_NS value; microsecond drops them.

On a value with less precision, the result ends in zeros: nanosecond(TIMESTAMP '2024-02-29 13:45:30.123456') is 30123456000, and nanosecond(TIMESTAMP_S '2024-02-29 13:45:30') is 30000000000. A DATE has no time, so its nanosecond is always 0.

Unlike second, millisecond and microsecond, nanosecond accepts a TIMESTAMP WITH TIME ZONE literal: nanosecond(TIMESTAMPTZ '2024-02-29 13:45:30.123456+02') is 30123456000.

To get only the fraction, take the remainder after dividing by 1000000000: nanosecond(tns) % 1000000000.

On an INTERVAL, nanosecond returns the seconds within the minute of its time part, in nanoseconds:

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, nanosecond(tns), nanosecond(t) FROM {TABLE} WHERE id = 4")
run("SELECT nanosecond(DATE 'infinity'), nanosecond(TIMESTAMP '-infinity'), nanosecond(DATE '2026-03-01'), "
    "nanosecond(TIME '00:00:59.999999'), nanosecond(TIMESTAMP_NS '1969-12-31 23:59:59.999999999'), "
    "nanosecond(TIMESTAMP_S '2024-02-29 13:45:30'), nanosecond(TIMETZ '13:45:30.123456+02:30'), "
    "nanosecond(TIMESTAMPTZ '2024-02-29 13:45:30.123456+02')")
run("SELECT nanosecond(INTERVAL '1 year 2 months 3 days 04:05:06.789'), nanosecond(INTERVAL '1.5 seconds'), "
    "nanosecond(INTERVAL '25 months 40 days 30 hours'), "
    "nanosecond(INTERVAL '-1 year -2 months -3 days -04:05:06.789')")
run("SELECT nanosecond(NULL)")
run("SELECT nanosecond('13:45:30.123456789')")
run("SELECT nanosecond(123)")

[(4, None, None)]
[(None, None, 0, 59999999000, 59999999999, 30000000000, 30123456000, 30123456000)]
[(6789000000, 1500000000, 0, -6789000000)]
InternalError: Binder Error: Could not choose a best candidate function for the function call "nanosecond("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 "nanosecond(STRING_LITERAL)". In order to select one, please add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'nanosecond(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The seconds of each event's nanosecond timestamp in nanoseconds, the fraction alone, and the same for the microsecond timestamp. Row 5 keeps its last nanosecond of 1969:

run(f"SELECT id, nanosecond(tns), nanosecond(tns) % 1000000000, nanosecond(ts) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 123456789, 123456789, 0), (2, 45123456000, 123456000, 45123456000), (3, 0, 0, 0), (4, None, None, None), (5, 59999999999, 999999999, 59999999000)]

Keep only the events whose nanosecond timestamp has digits below a microsecond, which a TIMESTAMP column couldn't store:

run(f"SELECT id FROM {TABLE} WHERE nanosecond(tns) % 1000 <> 0 ORDER BY id LIMIT 10")

[(1,), (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 nanosecond() is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes nanosecond() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes nanosecond() 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 nanosecond(tns) % 1000 <> 0")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ ((nanosecond(tns) % 1000) │
│           != 0)           │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            tns            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Combine it with a range on the raw column. In ts >= TIMESTAMP '2026-02-28 00:00:00' AND ts < TIMESTAMP '2026-03-01 00:00:00' AND nanosecond(tns) % 1000 <> 0, the range is still evaluated in VAST DataBase, so nanosecond() runs only on the rows of that day. The plan's FILTER step holds only the nanosecond() condition:

plan(f"SELECT id FROM {TABLE} WHERE ts >= TIMESTAMP '2026-02-28 00:00:00' AND ts < TIMESTAMP '2026-03-01 00:00:00' "
     "AND nanosecond(tns) % 1000 <> 0")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ ((nanosecond(tns) % 1000) │
│           != 0)           │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            tns            │
│             id            │
│                           │
└───────────────────────────┘

• Store the value if you filter on it often. Compute it when you write the rows and keep it in its own column: a comparison of that column with a constant is pushed down, nanosecond(tns) % 1000 <> 0 isn't.

Limitations and errors

• Not a part name for date_part or extract. date_part('nanosecond', ...) and extract(nanosecond FROM ...) fail with extract specifier "nanosecond" not recognized. Call nanosecond() instead.
• Nanoseconds only from TIMESTAMP_NS. Other types store at most microseconds, so their result always ends in 000.

run("SELECT date_part('nanosecond', TIMESTAMP '2024-02-29 13:45:30.123456')")
run(f"SELECT id, nanosecond(tns), nanosecond(ts), microsecond(tns) FROM {TABLE} ORDER BY id LIMIT 10")

InternalError: Conversion Error: extract specifier "nanosecond" not recognized
[(1, 123456789, 0, 123456), (2, 45123456000, 45123456000, 45123456), (3, 0, 0, 0), (4, None, None, None), (5, 59999999999, 59999999000, 0)]

Similar functions

run("SELECT nanosecond(TIMESTAMP_NS '2024-02-29 13:45:30.123456789'), "
    "second(TIMESTAMP_NS '2024-02-29 13:45:30.123456789'), "
    "millisecond(TIMESTAMP_NS '2024-02-29 13:45:30.123456789'), "
    "microsecond(TIMESTAMP_NS '2024-02-29 13:45:30.123456789'), "
    "strftime(TIMESTAMP_NS '2024-02-29 13:45:30.123456789', '%n')")

[(30123456789, 30, 30123, 30123456, '123456789')]

Clean up:

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

[]
[]

See also

• date_part: the other parts of a value, by name.
• Date and time functions: all the date and time functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.