to_centuries

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Intervals. Turns an integer number of centuries into an INTERVAL: to_centuries(1) is the same as INTERVAL 100 YEAR. Add it to a date or a timestamp, or subtract it.

Syntax

to_centuries(n)

Returns: INTERVAL.

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_numeric_to_centuries. 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 time capsules: the day each was sealed and the number of centuries until it opens, once as an INTEGER and once as a BIGINT. The last row 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_numeric_to_centuries"'

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}.capsules"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, sealed DATE, centuries INTEGER, centuries_big BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, DATE '2024-02-29', 1, 1), "
    "(2, DATE '2026-10-16', 2, 2), "
    "(3, DATE '1999-12-31', 0, 0), "
    "(4, DATE '2000-01-01', -1, -1), "
    "(5, NULL, NULL, NULL)")

[]
[]
[(5,)]

What it does

to_centuries(n) returns an interval of n × 100 years. The interval is held in months, so it adds calendar years: one century after 29 February 2024 is 29 February 2124.

Adding the interval to a DATE returns a TIMESTAMP, not a DATE.

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 to_centuries(NULL), to_centuries(0), to_centuries(1), to_centuries(-1)")
run("SELECT to_centuries(1789569)")
run("SELECT to_centuries(1789570)")
run(f"SELECT to_centuries('2'), to_centuries(CAST(2 AS SMALLINT)) FROM {TABLE} LIMIT 1")
run("SELECT to_centuries(CAST(2 AS BIGINT))")
run("SELECT to_centuries(1.5)")
run(f"SELECT to_centuries('abc') FROM {TABLE} LIMIT 1")

[(None, MonthDayNano(months=0, days=0, nanoseconds=0), MonthDayNano(months=1200, days=0, nanoseconds=0), MonthDayNano(months=-1200, days=0, nanoseconds=0))]
[(MonthDayNano(months=2147482800, days=0, nanoseconds=0),)]
OSError: Interval value 1789570 centuries out of range
[(MonthDayNano(months=2400, days=0, nanoseconds=0), MonthDayNano(months=2400, days=0, nanoseconds=0))]
InternalError: Binder Error: No function matches the given name and argument types 'to_centuries(BIGINT)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'to_centuries(DECIMAL(2,1))'. You might need to add explicit type casts.
OSError: Could not convert string 'abc' to INT32

The calls in What it does:

run("SELECT to_centuries(1) = INTERVAL 100 YEAR, to_centuries(1) = INTERVAL 1 CENTURY, to_centuries(1) = to_years(100)")
run("SELECT DATE '2026-10-16' + to_centuries(1), TIMESTAMP '2024-02-29 00:00:00' + to_centuries(1)")
run("SELECT typeof(to_centuries(1)), typeof(DATE '2026-10-16' + to_centuries(1))")

[(True, True, True)]
[(datetime.datetime(2126, 10, 16, 0, 0), datetime.datetime(2124, 2, 29, 0, 0))]
[('INTERVAL', 'TIMESTAMP')]

Examples

The day each capsule opens: the day it was sealed plus its number of centuries:

run(f"SELECT id, sealed, centuries, sealed + to_centuries(centuries) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, datetime.date(2024, 2, 29), 1, datetime.datetime(2124, 2, 29, 0, 0)), (2, datetime.date(2026, 10, 16), 2, datetime.datetime(2226, 10, 16, 0, 0)), (3, datetime.date(1999, 12, 31), 0, datetime.datetime(1999, 12, 31, 0, 0)), (4, datetime.date(2000, 1, 1), -1, datetime.datetime(1900, 1, 1, 0, 0)), (5, None, None, None)]

A BIGINT column doesn't match the function. Cast it to INTEGER:

run(f"SELECT id, to_centuries(centuries_big) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, sealed + to_centuries(CAST(centuries_big AS INTEGER)) FROM {TABLE} ORDER BY id LIMIT 10")

InternalError: Binder Error: No function matches the given name and argument types 'to_centuries(BIGINT)'. You might need to add explicit type casts.
[(1, datetime.datetime(2124, 2, 29, 0, 0)), (2, datetime.datetime(2226, 10, 16, 0, 0)), (3, datetime.datetime(1999, 12, 31, 0, 0)), (4, datetime.datetime(1900, 1, 1, 0, 0)), (5, None)]

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 to_centuries() is always computed by the Query Engine:

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

The plan of a WHERE condition on the function shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE to_centuries(centuries) IS NOT NULL")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (to_centuries(centuries) │
│        IS NOT NULL)       │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│         centuries         │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Compare the raw column with a constant. To find the capsules sealed more than a century before 17 October 2026, compute the cut-off date yourself and write sealed < DATE '1926-10-17'. A comparison of a column with a constant is pushed down to VAST DataBase, so rows that don't match never leave storage:

plan(f"SELECT id FROM {TABLE} WHERE sealed < DATE '1926-10-17'")

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

• Don't add the interval to the column. sealed + to_centuries(1) < DATE '2026-10-17' returns the same rows, but a column plus an interval runs in a FILTER step in the Query Engine.
• Store the number of centuries as an INTEGER. Then to_centuries() takes the column without a cast.

Limitations and errors

• INTEGER and smaller only. A BIGINT fails with No function matches the given name and argument types 'to_centuries(BIGINT)'; cast it with CAST(x AS INTEGER). A DECIMAL or DOUBLE fails the same way.
• Whole centuries only. For a fraction of a century, use a smaller unit, such as to_years or to_decades: to_years(150) for a century and a half.
• A range limit. to_centuries(1789570) and larger fail with Interval value ... centuries out of range.

run("SELECT to_centuries(CAST(5 AS BIGINT))")
run("SELECT to_centuries(CAST(CAST(5 AS BIGINT) AS INTEGER)), to_years(150)")

InternalError: Binder Error: No function matches the given name and argument types 'to_centuries(BIGINT)'. You might need to add explicit type casts.
[(MonthDayNano(months=6000, days=0, nanoseconds=0), MonthDayNano(months=1800, days=0, nanoseconds=0))]

Similar functions

run("SELECT to_centuries(1), to_years(100), to_decades(10), INTERVAL 100 YEAR")
run("SELECT to_centuries(1) = to_decades(10), to_centuries(1) = INTERVAL 1 CENTURY")

[(MonthDayNano(months=1200, days=0, nanoseconds=0), MonthDayNano(months=1200, days=0, nanoseconds=0), MonthDayNano(months=1200, days=0, nanoseconds=0), MonthDayNano(months=1200, days=0, nanoseconds=0))]
[(True, True)]

Clean up:

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

[]
[]

See also

• Numeric and math functions: all the numeric functions.
• to_centuries with the other interval constructors in date and time functions.
• to_years and to_decades.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.