Time zones

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Time zones. The VAST Query Engine works in UTC and has no time-zone support. A value written with a UTC offset is converted to UTC, and you compute with it as a TIMESTAMP. Named time zones, conversion between zones, current_date, current_time and the local-time functions aren't available. This page shows what works, what fails and what to write instead.

Syntax

The forms that run:

CAST(timestamptz_value AS TIMESTAMP)
CAST('YYYY-MM-DD HH:MM:SS+HH:MM' AS TIMESTAMP)
TIMESTAMPTZ 'YYYY-MM-DD HH:MM:SS+HH:MM'
CAST(timestamp_value AS TIMESTAMPTZ)
timezone(timestamp_value)
timezone_hour(timestamp_value)
timezone_minute(timestamp_value)

Returns: a cast to TIMESTAMP returns the UTC time as a TIMESTAMP. timezone, timezone_hour and timezone_minute return BIGINT, and on a TIMESTAMP they're always 0.

Not available: current_date, today(), current_time, get_current_time(), localtime, current_localtime(), localtimestamp, current_localtimestamp(), make_timestamptz(...), timezone(zone, value), value AT TIME ZONE zone and SET TimeZone. See Limitations and errors for what to write instead.

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_time_zones. 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 timestamp in UTC (ts) and a date (d). Row 4 has no values, and row 5 is in the future:

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

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 '2026-02-28 23:15:00', DATE '2026-02-28'), "
    "(2, TIMESTAMP '2026-03-01 06:30:00', DATE '2026-03-01'), "
    "(3, TIMESTAMP '2026-03-01 16:30:00', DATE '2026-03-01'), "
    "(4, NULL, NULL), "
    "(5, TIMESTAMP '2099-01-01 00:00:00', DATE '2099-01-01')")

[]
[]
[(5,)]

What it does

The Query Engine has one time zone: UTC.

• A value with a UTC offset is converted to UTC. TIMESTAMPTZ '2026-03-01 08:30:00+02' is 06:30 UTC. A TIMESTAMPTZ literal without an offset, or with Z, is taken as UTC. Casting a string with an offset straight to TIMESTAMP converts it the same way: CAST('2026-03-01 08:30:00+02' AS TIMESTAMP) is 2026-03-01 06:30:00.
• now() and current_timestamp return a TIMESTAMP WITH TIME ZONE in UTC (its text form ends in +00). See now.
• A TIMESTAMP WITH TIME ZONE value can be compared, cast and returned. TIMESTAMPTZ '2026-03-01 08:30:00+02' = TIMESTAMP '2026-03-01 06:30:00' is true. Most other date and time functions fail on it (year(now()), epoch(now()), age(now()), timezone_hour(now())), and so does adding an interval: cast it to TIMESTAMP first. epoch_ms, epoch_us, epoch_ns and nanosecond accept it.
• A TIMESTAMP has no time zone. timezone(ts), timezone_hour(ts) and timezone_minute(ts) return 0 for every TIMESTAMP value, at every precision, and NULL for a NULL.
• Named zones aren't known. TIMESTAMPTZ '2026-03-01 08:30:00 Europe/Lisbon' fails, and conversion to another zone (AT TIME ZONE, timezone('Europe/Lisbon', ts)) isn't available.

So store times as TIMESTAMP values in UTC, convert values with an offset to UTC when you write or query them, and convert to local time in your application.

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, timezone(ts), timezone_hour(ts), timezone_minute(ts) FROM {TABLE} WHERE id = 4")
run("SELECT timezone(TIMESTAMP '2024-02-29 13:45:30.123456'), timezone(TIMESTAMP_NS '2024-02-29 13:45:30.123456789'), "
    "timezone(TIMESTAMP_MS '2024-02-29 13:45:30.123'), timezone(TIMESTAMP_S '2024-02-29 13:45:30'), "
    "timezone(DATE 'infinity'), timezone(TIMESTAMP '-infinity')")
run("SELECT timezone(DATE '2026-03-01')")
run("SELECT timezone_hour(INTERVAL 1 DAY)")
run("SELECT timezone(TIME '13:45:30')")
run("SELECT timezone_hour(TIMETZ '08:30:00+02')")
run("SELECT timezone(NULL)")
run("SELECT CAST(TIMESTAMPTZ '2026-03-01 08:30:00' AS TIMESTAMP), CAST(TIMESTAMPTZ '2026-03-01 08:30:00Z' AS TIMESTAMP)")
run("SELECT TIMESTAMPTZ '2026-03-01 08:30:00 Europe/Lisbon'")
run("SELECT CAST(TIMETZ '08:30:00+02' AS TIME)")

[(4, None, None, None)]
[(0, 0, 0, 0, None, None)]
OSError: "date" units "timezone" not recognized
OSError: "interval" units "timezone_hour" not recognized
InternalError: Binder Error: No function matches the given name and argument types 'timezone(TIME)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'timezone_hour(TIME WITH TIME ZONE)'. You might need to add explicit type casts.
InternalError: Binder Error: Could not choose a best candidate function for the function call "timezone("NULL")". In order to select one, please add explicit type casts.
[(datetime.datetime(2026, 3, 1, 8, 30), datetime.datetime(2026, 3, 1, 8, 30))]
OSError: timestamp field value "2026-03-01 08:30:00 Europe/Lisbon" has a timestamp that is not UTC.
[(datetime.time(8, 30),)]

Examples

Convert values with an offset to UTC. Each TIMESTAMPTZ is cast to a TIMESTAMP, and so is a string with an offset:

run("SELECT CAST(TIMESTAMPTZ '2026-03-01 08:30:00+05:30' AS TIMESTAMP), "
    "CAST(TIMESTAMPTZ '2026-03-01 08:30:00-08' AS TIMESTAMP), "
    "CAST('2026-03-01 08:30:00+02' AS TIMESTAMP)")

[(datetime.datetime(2026, 3, 1, 3, 0), datetime.datetime(2026, 3, 1, 16, 30), datetime.datetime(2026, 3, 1, 6, 30))]

Keep the events from 8:30 on 1 March in UTC+2 onwards. The bound is cast to TIMESTAMP, so it's compared with the UTC values in ts:

run(f"SELECT id, ts FROM {TABLE} WHERE ts >= CAST(TIMESTAMPTZ '2026-03-01 08:30:00+02' AS TIMESTAMP) ORDER BY id LIMIT 10")

[(2, datetime.datetime(2026, 3, 1, 6, 30)), (3, datetime.datetime(2026, 3, 1, 16, 30)), (5, datetime.datetime(2099, 1, 1, 0, 0))]

Today's date, the current time and the current timestamp in UTC, in place of current_date, current_time and localtimestamp. The values change, so this block shows their types and keeps the events that are still in the future:

run("SELECT typeof(CAST(CAST(now() AS TIMESTAMP) AS DATE)), typeof(CAST(CAST(now() AS TIMESTAMP) AS TIME)), "
    "typeof(CAST(now() AS TIMESTAMP))")
run(f"SELECT id FROM {TABLE} WHERE ts > CAST(now() AS TIMESTAMP) ORDER BY id LIMIT 10")

[('DATE', 'TIME', 'TIMESTAMP')]
[(5,)]

Build a TIMESTAMP WITH TIME ZONE from its parts, in place of make_timestamptz: build a TIMESTAMP in UTC with make_timestamp and cast it:

run("SELECT CAST(make_timestamp(2026, 3, 1, 8, 30, 0.0) AS TIMESTAMPTZ)")

[(datetime.datetime(2026, 3, 1, 8, 30, tzinfo=zoneinfo.ZoneInfo(key='UTC')),)]

The time-zone parts of a TIMESTAMP are always 0:

run(f"SELECT id, timezone(ts), timezone_hour(ts), timezone_minute(ts) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 0, 0, 0), (2, 0, 0, 0), (3, 0, 0, 0), (4, None, None, None), (5, 0, 0, 0)]

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 of the column's type:

• A TIMESTAMP column compared with a TIMESTAMPTZ constant isn't pushed down. The scan sends ts for every row, and a FILTER step in the Query Engine makes the comparison. The same happens for ts < now().
• A TIMESTAMP column compared with a constant cast to TIMESTAMP is pushed down: the plan has no FILTER step, and rows that don't match never leave storage. This holds for CAST(now() AS TIMESTAMP) - INTERVAL 7 DAY and for today's date, CAST(CAST(now() AS TIMESTAMP) AS DATE), compared with a DATE column.
• timezone(ts), timezone_hour(ts) and timezone_minute(ts) are computed by the Query Engine: in WHERE, in a FILTER step.

The plan for a TIMESTAMPTZ bound has a FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE ts >= TIMESTAMPTZ '2026-03-01 00:00:00+00'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (CAST(ts AS TIMESTAMP WITH│
│  TIME ZONE) >= '2026-03-01│
│   00:00:00+00'::TIMESTAMP │
│       WITH TIME ZONE)     │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             ts            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Cast the constant, not the column. ts >= CAST(TIMESTAMPTZ '2026-03-01 02:00:00+02' AS TIMESTAMP) returns the same rows as ts >= TIMESTAMPTZ '2026-03-01 00:00:00+00', and it's pushed down to VAST DataBase. The plan has no FILTER step:

run(f"SELECT id FROM {TABLE} WHERE ts >= TIMESTAMPTZ '2026-03-01 00:00:00+00' ORDER BY id LIMIT 10")
run(f"SELECT id FROM {TABLE} WHERE ts >= CAST(TIMESTAMPTZ '2026-03-01 02:00:00+02' AS TIMESTAMP) ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE ts >= CAST(TIMESTAMPTZ '2026-03-01 02:00:00+02' AS TIMESTAMP)")

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

• Cast now() too. A comparison with now() itself, such as ts < now(), runs in a FILTER step. Compare with CAST(now() AS TIMESTAMP) instead: ts > CAST(now() AS TIMESTAMP) - INTERVAL 7 DAY for the last seven days, and d = CAST(CAST(now() AS TIMESTAMP) AS DATE) for today's rows. Both are pushed down:

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

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

• Store UTC TIMESTAMP columns. Convert values with an offset to UTC when you write them, so that queries compare the raw column with a constant.

Limitations and errors

The errors you can run without side effects:

run("SELECT now() - INTERVAL 1 DAY")
run("SELECT CAST(now() AS DATE)")
run("SET TimeZone = 'UTC'")
run("SELECT current_timezone()")
run("SELECT strptime('2024-02-29 13:45:30 Europe/Lisbon', '%Y-%m-%d %H:%M:%S %Z')")

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)
InternalError: The operator 'SET' type=69 is currently not supported.
InternalError: Catalog Error: Scalar Function with name current_timezone does not exist!
[(datetime.datetime(2024, 2, 29, 13, 45, 30),)]

Column types. A TIMESTAMP WITH TIME ZONE column can be created and written, but a query that returns it fails with Unsupported column type 'TIMESTAMP WITH TIME ZONE' of column 'v' in 'VAST_SCAN '.; its other columns can still be read. A TIME WITH TIME ZONE column can't be created. Use a TIMESTAMP column in UTC (data types in SQL):

TZ_TABLE = f"{SCHEMA}.tz_events"
run(f"CREATE TABLE IF NOT EXISTS {TZ_TABLE} (id BIGINT, v TIMESTAMPTZ)")
run(f"INSERT INTO {TZ_TABLE} VALUES (1, TIMESTAMPTZ '2026-03-01 08:30:00+02')")
run(f"SELECT id FROM {TZ_TABLE} LIMIT 10")
run(f"SELECT v FROM {TZ_TABLE} LIMIT 10")
run(f"CREATE TABLE IF NOT EXISTS {SCHEMA}.tz_times (id BIGINT, v TIME WITH TIME ZONE)")

[]
[(1,)]
[(1,)]
InternalError: Unsupported column type 'TIMESTAMP WITH TIME ZONE' of column 'v' in 'VAST_SCAN '.
InternalError: Binder Error: TIME WITH TIME ZONE type is not supported in table definitions. Column "v" has type: TIME WITH TIME ZONE

Similar functions

run("SELECT CAST(strptime('2024-02-29 13:45:30+02:30', '%Y-%m-%d %H:%M:%S%z') AS TIMESTAMP), "
    "CAST(to_timestamp(0) AS TIMESTAMP), epoch_ms(now()) > 0")

[(datetime.datetime(2024, 2, 29, 11, 15, 30), datetime.datetime(1970, 1, 1, 0, 0), True)]

Clean up:

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

[]
[]
[]
[]

See also

• now: the current time.
• Date and time functions: all the date and time functions, and what fails without time zones.
• Data types in SQL: the types a table can hold.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.