to_timestamp

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Building values. Turns a number of seconds since 1970-01-01 00:00:00 UTC (Unix time) into a timestamp with time zone, in UTC.

Syntax

to_timestamp(seconds)

Returns: TIMESTAMP WITH TIME ZONE, in UTC.

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_to_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 logins. Each has a time (ts), the same time in Unix seconds (n) and a duration in seconds with a fraction (f). 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_to_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}.logins"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, ts TIMESTAMP, n BIGINT, f DOUBLE)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, TIMESTAMP '2026-02-28 23:15:00', 1772320500, 1.5), "
    "(2, TIMESTAMP '2026-03-01 08:30:45', 1772353845, -0.25), "
    "(3, TIMESTAMP '1970-01-01 00:00:00', 0, 0.0), "
    "(4, NULL, NULL, NULL), "
    "(5, TIMESTAMP '1969-12-31 00:00:00', -86400, 0.000001)")

[]
[]
[(5,)]

What it does

to_timestamp(1772353845) is 2026-03-01 08:30:45 UTC. The argument is a DOUBLE, so it can carry a fraction of a second; the result keeps microseconds and rounds anything finer: to_timestamp(-1.5) is 1969-12-31 23:59:58.5, and to_timestamp(1.0000005) is 1970-01-01 00:00:01.000001.

The result is a TIMESTAMP WITH TIME ZONE in UTC. Your client receives it as a Python datetime with tzinfo set to UTC, and as text it ends in +00: '2026-03-01 08:30:45+00'. You can compare it with a TIMESTAMP or a TIMESTAMPTZ value, but functions such as year(), and + or - with an interval, don't take it. Cast it to TIMESTAMP to use it like any other timestamp: CAST(to_timestamp(n) AS TIMESTAMP) is the UTC time without a zone.

NULLs and edge cases

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

run("SELECT to_timestamp(NULL), to_timestamp(0), CAST(to_timestamp(-1.5) AS TIMESTAMP), "
    "CAST(to_timestamp(1.0000005) AS TIMESTAMP), CAST(to_timestamp(1772353845) AS VARCHAR), typeof(to_timestamp(0))")
run("SELECT to_timestamp('1772353845'), CAST(CAST(to_timestamp(253402300800) AS TIMESTAMP) AS VARCHAR)")
run("SELECT to_timestamp('NaN'::DOUBLE)")
run("SELECT to_timestamp(1e20)")
run("SELECT to_timestamp('abc')")
run("SELECT to_timestamp(DATE '2026-01-01')")

[(None, datetime.datetime(1970, 1, 1, 0, 0, tzinfo=zoneinfo.ZoneInfo(key='UTC')), datetime.datetime(1969, 12, 31, 23, 59, 58, 500000), datetime.datetime(1970, 1, 1, 0, 0, 1, 1), '2026-03-01 08:30:45+00', 'TIMESTAMP WITH TIME ZONE')]
[(datetime.datetime(2026, 3, 1, 8, 30, 45, tzinfo=zoneinfo.ZoneInfo(key='UTC')), '10000-01-01 00:00:00')]
OSError: Epoch seconds out of range for TIMESTAMP WITH TIME ZONE
OSError: Epoch seconds out of range for TIMESTAMP WITH TIME ZONE
OSError: Could not convert string 'abc' to DOUBLE
InternalError: Binder Error: No function matches the given name and argument types 'to_timestamp(DATE)'. You might need to add explicit type casts.

Examples

Each login's Unix time as a timestamp, first as returned and then cast to TIMESTAMP. Both match ts:

run(f"SELECT id, ts, to_timestamp(n), CAST(to_timestamp(n) AS TIMESTAMP) FROM {TABLE} WHERE id <= 3 ORDER BY id LIMIT 10")

[(1, datetime.datetime(2026, 2, 28, 23, 15), datetime.datetime(2026, 2, 28, 23, 15, tzinfo=zoneinfo.ZoneInfo(key='UTC')), datetime.datetime(2026, 2, 28, 23, 15)), (2, datetime.datetime(2026, 3, 1, 8, 30, 45), datetime.datetime(2026, 3, 1, 8, 30, 45, tzinfo=zoneinfo.ZoneInfo(key='UTC')), datetime.datetime(2026, 3, 1, 8, 30, 45)), (3, datetime.datetime(1970, 1, 1, 0, 0), datetime.datetime(1970, 1, 1, 0, 0, tzinfo=zoneinfo.ZoneInfo(key='UTC')), datetime.datetime(1970, 1, 1, 0, 0))]

Fractions of a second, from a DOUBLE column:

run(f"SELECT id, f, CAST(to_timestamp(f) AS TIMESTAMP) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 1.5, datetime.datetime(1970, 1, 1, 0, 0, 1, 500000)), (2, -0.25, datetime.datetime(1969, 12, 31, 23, 59, 59, 750000)), (3, 0.0, datetime.datetime(1970, 1, 1, 0, 0)), (4, None, None), (5, 1e-06, datetime.datetime(1970, 1, 1, 0, 0, 0, 1))]

Keep the logins after the start of 2026, given in Unix seconds:

run(f"SELECT id FROM {TABLE} WHERE CAST(to_timestamp(n) AS TIMESTAMP) > TIMESTAMP '2026-01-01 00:00:00' "
    "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 a constant, such as CAST(to_timestamp(1772300000) AS TIMESTAMP), the Query Engine computes the timestamp once, before the scan. ts > CAST(to_timestamp(1772300000) AS TIMESTAMP) 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 a column, in the SELECT list, the scan reads the column and the Query Engine converts it for each row it returns. In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine converts it and keeps the rows that match.

The plan of the WHERE example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE CAST(to_timestamp(n) AS TIMESTAMP) > TIMESTAMP '2026-01-01 00:00:00'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (CAST(to_timestamp(CAST(n │
│  AS DOUBLE)) AS TIMESTAMP)│
│  > '2026-01-01 00:00:00': │
│        :TIMESTAMP)        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             n             │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Compare Unix seconds as numbers. If the column holds Unix seconds, compare it with the number of seconds of your bound: n > 1767225600 (2026-01-01 00:00:00 UTC) returns the same rows as the WHERE example, and it's pushed down to VAST DataBase.
• Convert the bound, not the column. On a TIMESTAMP column, ts > CAST(to_timestamp(1772300000) AS TIMESTAMP) is pushed down too.
• Store times as TIMESTAMP. A filter on to_timestamp(n) converts every row in the Query Engine.

The two plans have no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE n > 1767225600")
plan(f"SELECT id FROM {TABLE} WHERE ts > CAST(to_timestamp(1772300000) AS TIMESTAMP)")

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

Limitations and errors

• Cast before you compute. On the result itself, functions such as year() fail with an Extension Autoloading Error, and + INTERVAL 1 DAY fails with No function matches the given name and argument types '+(TIMESTAMP WITH TIME ZONE, INTERVAL)'. Cast it to TIMESTAMP first. See time zones.
• Seconds only, one argument. There's no form with a format string or a time zone: to_timestamp('2026-03-01', 'YYYY-MM-DD') fails with No function matches the given name and argument types 'to_timestamp(STRING_LITERAL, STRING_LITERAL)'. To parse text, use strptime. For milliseconds since 1970, use epoch_ms; for microseconds, make_timestamp.
• Other SQL dialects' names aren't available. unix_timestamp fails with Scalar Function with name unix_timestamp does not exist!; for the reverse conversion, use epoch.

run("SELECT to_timestamp(0) + INTERVAL 1 DAY")
run("SELECT year(CAST(to_timestamp(0) AS TIMESTAMP)), CAST(to_timestamp(0) AS TIMESTAMP) + INTERVAL 1 DAY")
run("SELECT to_timestamp('2026-03-01', 'YYYY-MM-DD')")
run("SELECT strptime('2026-03-01', '%Y-%m-%d')")

InternalError: Binder Error: No function matches the given name and argument types '+(TIMESTAMP WITH TIME ZONE, INTERVAL)'. You might need to add explicit type casts.
[(1970, datetime.datetime(1970, 1, 2, 0, 0))]
InternalError: Binder Error: No function matches the given name and argument types 'to_timestamp(STRING_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.
[(datetime.datetime(2026, 3, 1, 0, 0),)]

Similar functions

run("SELECT CAST(to_timestamp(1735566330.123456) AS TIMESTAMP), epoch_ms(1735566330123), "
    "make_timestamp(1735566330123456), epoch(TIMESTAMP '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), datetime.datetime(2024, 12, 30, 13, 45, 30, 123456), 1735566330.123456)]

Clean up:

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

[]
[]

See also

• Time zones: what works with TIMESTAMP WITH TIME ZONE values.
• epoch: the Unix seconds of a timestamp, the reverse of to_timestamp.
• Date and time functions: all the date and time functions.
• Where filters run and pushdowns.