to_decades

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Interval constructors. Returns an INTERVAL of n decades. Add it to a date or timestamp to move it by whole decades.

Syntax

to_decades(n)

Returns: INTERVAL, held as n × 120 months.

Same as: to_years(n * 10); for one decade, the literal INTERVAL 1 DECADE.

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_to_decades. 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 four spans. Each has a count n (an INTEGER) and a start time ts. Row 1 starts on 29 February, and 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_to_decades"'

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}.spans"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, n INTEGER, ts TIMESTAMP)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 1, TIMESTAMP '2024-02-29 00:00:00'), "
    "(2, -2, TIMESTAMP '2026-01-31 12:00:00'), "
    "(3, 0, TIMESTAMP '2026-03-01 08:30:45'), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

to_decades(n) returns an interval of n decades, held as n × 120 months with no days and no time. The ADBC driver shows it as a MonthDayNano value: to_decades(2) is MonthDayNano(months=240, days=0, nanoseconds=0).

Adding the interval to a date or timestamp moves it by whole calendar months, so the time of day and, where the month allows, the day of the month are kept: TIMESTAMP '2024-02-29 00:00:00' + to_decades(1) is 2034-02-28 00:00:00. 2034 isn't a leap year, so 29 February becomes 28 February. A DATE plus the interval is a TIMESTAMP: DATE '2026-01-31' + to_decades(1) is 2036-01-31 00:00:00.

Because the interval is a number of months, its length in seconds depends on how you count a month. epoch counts a year as 365.25 days, and epoch_us counts a month as 30 days:

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, to_decades(n) FROM {TABLE} WHERE id = 4")
run("SELECT to_decades(NULL), to_decades(0), to_decades(-1), to_decades(17895697), to_decades('2'), to_decades(CAST(2 AS SMALLINT))")
run("SELECT to_decades(17895698)")
run("SELECT to_decades(CAST(2 AS BIGINT))")
run("SELECT to_decades(1.5)")
run("SELECT to_decades(true)")

[(4, None)]
[(None, MonthDayNano(months=0, days=0, nanoseconds=0), MonthDayNano(months=-120, days=0, nanoseconds=0), MonthDayNano(months=2147483640, days=0, nanoseconds=0), MonthDayNano(months=240, days=0, nanoseconds=0), MonthDayNano(months=240, days=0, nanoseconds=0))]
OSError: Interval value 17895698 decades out of range
InternalError: Binder Error: No function matches the given name and argument types 'to_decades(BIGINT)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'to_decades(DECIMAL(2,1))'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'to_decades(BOOLEAN)'. You might need to add explicit type casts.

Examples

The interval for each row's count, and the start time moved by it:

run(f"SELECT id, n, to_decades(n), ts + to_decades(n) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 1, MonthDayNano(months=120, days=0, nanoseconds=0), datetime.datetime(2034, 2, 28, 0, 0)), (2, -2, MonthDayNano(months=-240, days=0, nanoseconds=0), datetime.datetime(2006, 1, 31, 12, 0)), (3, 0, MonthDayNano(months=0, days=0, nanoseconds=0), datetime.datetime(2026, 3, 1, 8, 30, 45)), (4, None, None, None)]

id is a BIGINT, which to_decades doesn't take. Cast it to INTEGER first:

run(f"SELECT id, to_decades(CAST(id AS INTEGER)) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT to_decades(id) FROM {TABLE} LIMIT 1")

[(1, MonthDayNano(months=120, days=0, nanoseconds=0)), (2, MonthDayNano(months=240, days=0, nanoseconds=0)), (3, MonthDayNano(months=360, days=0, nanoseconds=0)), (4, MonthDayNano(months=480, days=0, nanoseconds=0))]
InternalError: Binder Error: No function matches the given name and argument types 'to_decades(BIGINT)'. You might need to add explicit type casts.

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_decades() of a column is always computed by the Query Engine:

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

The plan of a condition on ts + to_decades(n) shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE ts + to_decades(n) > TIMESTAMP '2030-01-01 00:00:00'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  ((ts + to_decades(n)) >  │
│   '2030-01-01 00:00:00':  │
│        :TIMESTAMP)        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             ts            │
│             n             │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Put the interval on the constant side. ts > TIMESTAMP '2030-01-01 00:00:00' - to_decades(1) compares the raw column with a value the Query Engine computes once, so the comparison is pushed down to VAST DataBase and rows that don't match never leave storage. The plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE ts > TIMESTAMP '2030-01-01 00:00:00' - to_decades(1)")

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

• Store the end time if you filter on it often. If each row's span is n decades, compute ts + to_decades(n) when you write the row and keep it in its own TIMESTAMP column: a comparison of that column with a constant is pushed down.

Limitations and errors

• INTEGER only. A BIGINT fails with No function matches the given name and argument types 'to_decades(BIGINT)'; cast it with CAST(x AS INTEGER). A fraction fails too: for part of a decade, use to_years or to_months with a whole number of years or months.
• Range. n can be at most 17895697 (and at least -17895697), because the interval's months must fit in an INTEGER. Beyond that it fails with Interval value ... decades out of range.
• No time-zone support. Adding an interval to a TIMESTAMP WITH TIME ZONE, such as now(), fails. Cast the value to TIMESTAMP first; see time zones.

Similar functions

run("SELECT to_decades(1) = INTERVAL 1 DECADE, to_decades(1) = to_years(10), to_decades(1) = to_months(120)")
run("SELECT epoch(to_decades(1)), epoch_us(to_decades(1)), normalized_interval(to_decades(100))")
run("SELECT TIMESTAMP '2024-02-29 00:00:00' + to_decades(1), DATE '2026-01-31' + to_decades(1)")

[(True, True, True)]
[(315576000.0, 311040000000000, MonthDayNano(months=12000, days=0, nanoseconds=0))]
[(datetime.datetime(2034, 2, 28, 0, 0), datetime.datetime(2036, 1, 31, 0, 0))]

Clean up:

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

[]
[]

See also

• Date and time functions: all the date and time functions, and the interval constructors.
• plus: adding an interval to a date or timestamp.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.