make_time

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Building values. Builds a time of day from an hour, a minute and a number of seconds, which can have a fraction.

Syntax

make_time(hour, minute, seconds)

Returns: TIME.

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_make_time. 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 sensor readings, with the hour (h), minute (mi) and seconds (sec) at which each was taken, and the day (d). 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_make_time"'

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}.readings"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, d DATE, h BIGINT, mi BIGINT, sec DOUBLE)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, DATE '2026-03-01', 8, 30, 15.25), "
    "(2, DATE '2026-03-01', 23, 59, 59.999999), "
    "(3, DATE '2026-03-02', 0, 0, 0.0), "
    "(4, NULL, NULL, NULL, NULL), "
    "(5, DATE '2026-03-02', 12, 0, 60.0)")

[]
[]
[(5,)]

What it does

make_time(13, 45, 30.5) is 13:45:30.5, the same as TIME '13:45:30.5'. The seconds are a DOUBLE, so they can carry a fraction; the result keeps microseconds and rounds anything finer: make_time(8, 30, 1.0000005) is 08:30:01.000001.

Two values outside the usual range are accepted and carry over:

• Seconds of 60 carry into the next minute: make_time(8, 30, 60) is 08:31:00. So do seconds that round up to 60, such as 59.9999999.
• Hour 24 gives midnight: make_time(24, 0, 0) is 00:00:00.

Anything else outside the range fails with Time out of range, followed by the parts as given: an hour of 25 or -1, a minute of 60 or -1, or negative seconds.

NULLs and edge cases

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

run("SELECT make_time(NULL, 30, 15.25), make_time(8, NULL, 0), make_time(8, 30, NULL)")
run("SELECT make_time(0, 0, 0), make_time(23, 59, 59.999999), make_time(24, 0, 0), make_time(8, 30, 60), "
    "make_time(8, 30, 59.9999999), make_time(8, 30, 1.0000005)")
run("SELECT make_time(25, 0, 0)")
run("SELECT make_time(8, 60, 0)")
run("SELECT make_time(8, 30, -0.5)")
run("SELECT make_time(8.6, 30, 0)")
run("SELECT make_time(8, 30)")
run("SELECT make_time('abc', 30, 15.25)")

[(None, None, None)]
[(datetime.time(0, 0), datetime.time(23, 59, 59, 999999), datetime.time(0, 0), datetime.time(8, 31), datetime.time(8, 31), datetime.time(8, 30, 1, 1))]
OSError: Time out of range: 25:0:0.0
OSError: Time out of range: 8:60:0.0
OSError: Time out of range: 8:30:0.-500000
InternalError: Binder Error: No function matches the given name and argument types 'make_time(DECIMAL(2,1), INTEGER_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'make_time(INTEGER_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.
OSError: Could not convert string 'abc' to INT64

Examples

The time of each reading. Row 5's 60 seconds carry into the next minute:

run(f"SELECT id, make_time(h, mi, sec) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, datetime.time(8, 30, 15, 250000)), (2, datetime.time(23, 59, 59, 999999)), (3, datetime.time(0, 0)), (4, None), (5, datetime.time(12, 1))]

The full timestamp of each reading: a DATE plus a TIME is a TIMESTAMP:

run(f"SELECT id, d + make_time(h, mi, sec) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, datetime.datetime(2026, 3, 1, 8, 30, 15, 250000)), (2, datetime.datetime(2026, 3, 1, 23, 59, 59, 999999)), (3, datetime.datetime(2026, 3, 2, 0, 0)), (4, None), (5, datetime.datetime(2026, 3, 2, 12, 1))]

Keep the readings taken after noon:

run(f"SELECT id FROM {TABLE} WHERE make_time(h, mi, sec) > TIME '12:00:00' ORDER BY id LIMIT 10")

[(2,), (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 columns, so make_time() on columns is always computed by the Query Engine:

• In the SELECT list, the scan reads the columns and the Query Engine builds the time for each row it returns.
• In WHERE, the scan sends the columns for every row, and a FILTER step in the Query Engine builds the time 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 make_time(h, mi, sec) > TIME '12:00:00'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #3            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (make_time(h, mi, sec) >  │
│     '12:00:00'::TIME)     │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             h             │
│             mi            │
│            sec            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Store a TIMESTAMP, not its parts. A range on a TIMESTAMP column, such as ts >= TIMESTAMP '2026-03-01 12:00:00', is compared with the constants in VAST DataBase, so rows that don't match never leave storage. A time built from parts is computed row by row.
• Filter on a part when that's enough. h >= 12 (from noon on) is a comparison of a column with a constant, and it's pushed down: its plan has no FILTER step.

plan(f"SELECT id FROM {TABLE} WHERE h >= 12")

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

Limitations and errors

• Whole hours and minutes. The hour and minute must be integers: a decimal fails with No function matches the given name and argument types. Put any fraction in the seconds.
• Three arguments. There's no form with only hours and minutes; pass 0 for the seconds.
• No time zone. make_time builds a TIME without a time zone.
• Not a number of seconds since midnight. To turn a count of seconds into a time, add an interval to midnight: TIME '00:00:00' + to_seconds(n).

Similar functions

run("SELECT make_time(13, 45, 30.5), TIME '13:45:30.5', CAST('13:45:30.5' AS TIME), "
    "CAST(make_timestamp(2024, 2, 29, 13, 45, 30.5) AS TIME)")
run("SELECT TIME '00:00:00' + to_seconds(49530.5)")

[(datetime.time(13, 45, 30, 500000), datetime.time(13, 45, 30, 500000), datetime.time(13, 45, 30, 500000), datetime.time(13, 45, 30, 500000))]
[(datetime.time(13, 45, 30, 500000),)]

Clean up:

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

[]
[]

See also

• make_date, make_timestamp and make_timestamp_ns: build a date or a timestamp.
• hour, minute and second: take a time apart.
• Date and time functions: all the date and time functions.
• Where filters run and pushdowns.