epoch_ms

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Epoch values. Returns the number of whole milliseconds since 1970-01-01 00:00:00 of a date or timestamp, as a BIGINT. It also works the other way: given a BIGINT count of milliseconds, it returns the TIMESTAMP.

Syntax

epoch_ms(value)
epoch_ms(milliseconds)

Returns: BIGINT; TIMESTAMP for epoch_ms(milliseconds).

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_epoch_ms. 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), a timestamp with nanoseconds (tns) and a Unix time in milliseconds (ms). 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_epoch_ms"'

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, ms BIGINT)")
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), 1772320500000), "
    "(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), 1772353845123), "
    "(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), 0), "
    "(4, NULL, 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), -1)")

[]
[]
[(5,)]

What it does

epoch_ms has two forms:

• From a date or time value to milliseconds. On a TIMESTAMP or DATE, the milliseconds since 1970-01-01 00:00:00; on a TIME, the milliseconds since midnight; on an INTERVAL, its length in milliseconds. The digits finer than a millisecond are dropped: epoch_ms(TIMESTAMP '2024-02-29 13:45:30.123456') is 1709214330123.
• From milliseconds to a timestamp. On a BIGINT, the TIMESTAMP that many milliseconds after 1970-01-01 00:00:00: epoch_ms(1772353845123) is 2026-03-01 08:30:45.123, and epoch_ms(-1) is 1969-12-31 23:59:59.999. Use it to read Unix times in milliseconds, as many systems write them.

On a TIMESTAMP WITH TIME ZONE, the milliseconds count from 1970-01-01 00:00:00 UTC, so the offset is applied: epoch_ms(TIMESTAMPTZ '2024-02-29 13:45:30.123456+02') is 1709207130123. On a TIME WITH TIME ZONE, the offset isn't applied: epoch_ms(TIMETZ '13:45:30.123456+02:30') is 49530123.

On an INTERVAL, epoch_ms counts a month as 30 days and a year as 12 months: epoch_ms(INTERVAL '1 year') is 31104000000 (360 days). epoch counts a year as 365.25 days instead.

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, epoch_ms(ts), epoch_ms(ms) FROM {TABLE} WHERE id = 4")
run("SELECT epoch_ms(NULL), epoch_ms(DATE 'infinity'), epoch_ms(TIMESTAMP '-infinity'), "
    "epoch_ms(TIMESTAMP '1969-12-31 23:59:59.999999'), epoch_ms(TIMESTAMP '1969-12-31 23:59:59.9995')")
run("SELECT epoch_ms(DATE '0001-01-01'), epoch_ms(INTERVAL '1 month'), epoch_ms(INTERVAL '1 year 2 months 3 days 04:05:06.789')")
run("SELECT epoch_ms(0), CAST(epoch_ms(9223372036854) AS VARCHAR)")
run("SELECT epoch_ms(9223372036854775807)")
run("SELECT epoch_ms(1.5e12)")
run("SELECT epoch_ms('1772320500000')")
run("SELECT epoch_ms(true)")

[(4, None, None)]
[(None, None, None, 0, 0)]
[(-62135596800000, 2592000000, 36561906789)]
[(datetime.datetime(1970, 1, 1, 0, 0), '2262-04-11 23:47:16.854')]
OSError: Could not convert Timestamp(MS) to Timestamp(US)
InternalError: Binder Error: No function matches the given name and argument types 'epoch_ms(DOUBLE)'. You might need to add explicit type casts.
InternalError: Binder Error: Could not choose a best candidate function for the function call "epoch_ms(STRING_LITERAL)". In order to select one, please add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'epoch_ms(BOOLEAN)'. You might need to add explicit type casts.

Examples

The milliseconds since 1970 of each event's date and timestamp, and the milliseconds since midnight of its time:

run(f"SELECT id, epoch_ms(d), epoch_ms(ts), epoch_ms(t) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 1772236800000, 1772320500000, 83700000), (2, 1772323200000, 1772353845123, 30645123), (3, 1735516800000, 1735560000000, 43200000), (4, None, None, None), (5, -86400000, 0, 86399999)]

Turn the Unix times in milliseconds into timestamps:

run(f"SELECT id, ms, epoch_ms(ms) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 1772320500000, datetime.datetime(2026, 2, 28, 23, 15)), (2, 1772353845123, datetime.datetime(2026, 3, 1, 8, 30, 45, 123000)), (3, 0, datetime.datetime(1970, 1, 1, 0, 0)), (4, None, None), (5, -1, datetime.datetime(1969, 12, 31, 23, 59, 59, 999000))]

Keep only the events after a Unix time in milliseconds. This works, but Make it faster has a faster way to write it:

run(f"SELECT id FROM {TABLE} WHERE epoch_ms(ts) > 1772300000000 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, so epoch_ms() of a column is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes epoch_ms() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes epoch_ms() and keeps the rows that match. No rows are skipped in storage. This is the same for both forms.

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

plan(f"SELECT id FROM {TABLE} WHERE epoch_ms(ts) > 1772300000000")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│      (epoch_ms(ts) >      │
│       1772300000000)      │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             ts            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Apply epoch_ms to the constant, not to the column. ts > epoch_ms(1772300000000) returns the same rows as epoch_ms(ts) > 1772300000000. The Query Engine computes the constant once, so the comparison of the raw column is pushed down to VAST DataBase, and the plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE ts > epoch_ms(1772300000000)")
run(f"SELECT id FROM {TABLE} WHERE ts > epoch_ms(1772300000000) ORDER BY id LIMIT 10")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
[(1,), (2,)]

• Store Unix times as TIMESTAMP. If you load milliseconds, convert them once with epoch_ms(ms) when you write the rows. A TIMESTAMP column can be filtered with pushed-down comparisons and read with every date and time function.

Limitations and errors

• Milliseconds only. The reverse form loses the microseconds: epoch_ms(epoch_ms(TIMESTAMP '2026-03-01 08:30:45.123456')) is 2026-03-01 08:30:45.123. For microseconds, use epoch_us and make_timestamp.
• Only epoch_ms has the reverse form. epoch_us(1772353845123456), epoch_ns(...) and epoch(...) of a number fail with No function matches the given name and argument types. Use make_timestamp for microseconds, make_timestamp_ns for nanoseconds and to_timestamp for seconds.
• Whole milliseconds as BIGINT. A DOUBLE or DECIMAL count fails; cast it to BIGINT first.
• One argument. epoch_ms(ts, ts) fails with No function matches the given name and argument types 'epoch_ms(TIMESTAMP, TIMESTAMP)'.

run("SELECT epoch_ms(epoch_ms(TIMESTAMP '2026-03-01 08:30:45.123456'))")
run("SELECT epoch_us(1772353845123456)")
run("SELECT epoch_ms(CAST(1.5e12 AS BIGINT))")
run(f"SELECT epoch_ms(ts, ts) FROM {TABLE} LIMIT 1")

[(datetime.datetime(2026, 3, 1, 8, 30, 45, 123000),)]
InternalError: Binder Error: No function matches the given name and argument types 'epoch_us(INTEGER_LITERAL)'. You might need to add explicit type casts.
[(datetime.datetime(2017, 7, 14, 2, 40),)]
InternalError: Binder Error: No function matches the given name and argument types 'epoch_ms(TIMESTAMP, TIMESTAMP)'. You might need to add explicit type casts.

Similar functions

run("SELECT epoch_ms(TIMESTAMP '2024-12-30 13:45:30.123456'), epoch(TIMESTAMP '2024-12-30 13:45:30.123456'), "
    "epoch_us(TIMESTAMP '2024-12-30 13:45:30.123456'), epoch_ns(TIMESTAMP '2024-12-30 13:45:30.123456')")
run("SELECT epoch_ms(1735566330123), make_timestamp(1735566330123456), CAST(to_timestamp(1735566330.123456) AS TIMESTAMP)")

[(1735566330123, 1735566330.123456, 1735566330123456, 1735566330123456000)]
[(datetime.datetime(2024, 12, 30, 13, 45, 30, 123000), 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

• epoch, epoch_us and epoch_ns: seconds, microseconds and nanoseconds.
• Date and time functions: all the date and time functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.