now

Applies to VAST Cluster 5.5.

Functions › Date and time functions › The current time. Returns the current date and time, in UTC, as a TIMESTAMP WITH TIME ZONE. current_timestamp, get_current_timestamp() and transaction_timestamp() are the same function.

Syntax

now()
current_timestamp
get_current_timestamp()
transaction_timestamp()

now() takes no arguments. current_timestamp is written without parentheses.

Returns: TIMESTAMP WITH TIME ZONE, in UTC. The ADBC driver returns it as a Python datetime with the UTC time zone.

Aliases: current_timestamp, get_current_timestamp, transaction_timestamp.

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

The examples compare with the current time but never print it, so their output is the same whenever you run them. This block connects, defines the run() and plan() helpers, and creates a table of four events, each with a timestamp (ts) and a date (d). Rows 1 and 2 are in the past, row 3 has no values, and row 4 is at the end of 2099:

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_now"'

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, d DATE)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, TIMESTAMP '2024-02-29 13:45:30', DATE '2024-02-29'), "
    "(2, TIMESTAMP '2026-03-01 08:30:45', DATE '2026-03-01'), "
    "(3, NULL, NULL), "
    "(4, TIMESTAMP '2099-12-31 00:00:00', DATE '2099-12-31')")

[]
[]
[(4,)]

What it does

now() returns the current date and time as a TIMESTAMP WITH TIME ZONE in UTC; as text, it ends in +00. The four names are the same function, and give the same value within a statement.

The VAST Query Engine has no time-zone support, so few functions take a TIMESTAMP WITH TIME ZONE. What works on now() directly:

• comparisons with a TIMESTAMP, a DATE or a column of either, such as ts < now();
• epoch_ms, epoch_us, epoch_ns, nanosecond and isfinite;
• CAST(now() AS TIMESTAMP) and CAST(now() AS VARCHAR).

For anything else, including arithmetic with an INTERVAL, year, date_trunc and strftime, cast it to TIMESTAMP first. The result is the current time in UTC, as a TIMESTAMP. From there, cast to DATE for today's date, or to TIME for the time of day:

NULLs and edge cases

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

run("SELECT CAST(now() AS TIMESTAMP) > NULL, now() = current_timestamp, now() = get_current_timestamp(), "
    "now() = transaction_timestamp(), CAST(now() AS VARCHAR) LIKE '%+00'")
run("SELECT now(1)")
run("SELECT current_timestamp()")
run("SELECT TIMESTAMP 'now'")
run("SELECT now() - INTERVAL 1 DAY")
run("SELECT CAST(now() AS DATE)")

[(None, True, True, True, True)]
InternalError: Binder Error: No function matches the given name and argument types 'now(INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Catalog Error: Scalar Function with name current_timestamp does not exist!
OSError: invalid timestamp field format: "now", expected format is (YYYY-MM-DD HH:MM:SS[.US][±HH:MM| ZONE])
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.
OSError: Unimplemented type for cast (TIMESTAMP WITH TIME ZONE -> DATE)

Examples

The type of each name, and of the value cast to TIMESTAMP, DATE and TIME:

run("SELECT typeof(now()), typeof(current_timestamp), typeof(get_current_timestamp()), typeof(transaction_timestamp())")
run("SELECT typeof(CAST(now() AS TIMESTAMP)), typeof(CAST(CAST(now() AS TIMESTAMP) AS DATE)), "
    "typeof(CAST(CAST(now() AS TIMESTAMP) AS TIME))")

[('TIMESTAMP WITH TIME ZONE', 'TIMESTAMP WITH TIME ZONE', 'TIMESTAMP WITH TIME ZONE', 'TIMESTAMP WITH TIME ZONE')]
[('TIMESTAMP', 'DATE', 'TIME')]

Which events are in the past:

run(f"SELECT id, ts < now(), ts < CAST(now() AS TIMESTAMP), d < CAST(CAST(now() AS TIMESTAMP) AS DATE) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, True, True, True), (2, True, True, True), (3, None, None, None), (4, False, False, False)]

The events after a week ago. Only row 4, in 2099, is that recent:

run(f"SELECT id FROM {TABLE} WHERE ts > CAST(now() AS TIMESTAMP) - INTERVAL 7 DAY ORDER BY id LIMIT 10")

[(4,)]

The functions that take now() directly, and those that take it cast to TIMESTAMP:

run("SELECT epoch_ms(now()) > 0, typeof(epoch_ms(now())), isfinite(now()), nanosecond(now()) >= 0")
run("SELECT year(CAST(now() AS TIMESTAMP)) >= 2026, typeof(date_trunc('day', CAST(now() AS TIMESTAMP))), "
    "CAST(now() AS TIMESTAMP) - INTERVAL 1 DAY < CAST(now() AS TIMESTAMP)")

[(True, 'BIGINT', True, True)]
[(True, 'DATE', True)]

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). now() doesn't depend on any row, so the Query Engine computes CAST(now() AS TIMESTAMP), and an expression built on it such as - INTERVAL 7 DAY, once, before the scan. A comparison of a column with that value is a comparison with a constant, which VAST DataBase evaluates. The plan of the last-week example has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE ts > CAST(now() AS TIMESTAMP) - INTERVAL 7 DAY")

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

Compared without the cast, as in ts < now(), the column is a TIMESTAMP and now() is a TIMESTAMP WITH TIME ZONE. The scan sends ts for every row, and a FILTER step in the Query Engine compares them:

plan(f"SELECT id FROM {TABLE} WHERE ts < now()")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (CAST(ts AS TIMESTAMP WITH│
│  TIME ZONE) < '2026-10-17 │
│      17:39:42.14+00':     │
│ :TIMESTAMP WITH TIME ZONE)│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             ts            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Cast now(), not the column. Write ts < CAST(now() AS TIMESTAMP), not ts < now(): the cast value is a constant, so the comparison is pushed down to VAST DataBase and rows that don't match never leave storage.
• Compare a DATE column with today's date the same way. d = CAST(CAST(now() AS TIMESTAMP) AS DATE) is pushed down too:

plan(f"SELECT id FROM {TABLE} WHERE d = CAST(CAST(now() AS TIMESTAMP) AS DATE)")

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

Limitations and errors

• UTC only. now() is always in UTC, and there's no time-zone setting or conversion. To show local times, convert in your client. See time zones.
• Few functions take a TIMESTAMP WITH TIME ZONE. Arithmetic fails with No function matches the given name and argument types '-(TIMESTAMP WITH TIME ZONE, INTERVAL)', strftime(now(), ...) with No function matches ... 'strftime(TIMESTAMP WITH TIME ZONE, STRING_LITERAL)', and functions such as year(now()) and date_trunc('day', now()) with an error. Cast to TIMESTAMP first.
• No current_date, today(), current_time, localtime or localtimestamp. They fail with a Catalog Error. Use the casts in What it does.
• No precision argument. now(1) and current_timestamp(3) fail. To drop the fraction of a second, use date_trunc on the cast value: date_trunc('second', CAST(now() AS TIMESTAMP)).

run("SELECT strftime(now(), '%Y')")
run("SELECT strftime(CAST(now() AS TIMESTAMP), '%Y') >= '2026'")
run("SELECT current_timestamp(3)")
run("SELECT typeof(date_trunc('second', CAST(now() AS TIMESTAMP)))")

InternalError: Binder Error: No function matches the given name and argument types 'strftime(TIMESTAMP WITH TIME ZONE, STRING_LITERAL)'. You might need to add explicit type casts.
[(True,)]
InternalError: Catalog Error: Scalar Function with name current_timestamp does not exist!
[('TIMESTAMP',)]

Similar functions

run("SELECT typeof(CAST(now() AS TIMESTAMP)), typeof(CAST(CAST(now() AS TIMESTAMP) AS DATE)), typeof(epoch_ms(now()))")

[('TIMESTAMP', 'DATE', 'BIGINT')]

Clean up:

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

[]
[]

See also

• Time zones: what works on TIMESTAMP WITH TIME ZONE values, and what to write instead.
• Date and time functions: all the date and time functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.