normalized_interval

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Adding and interval arithmetic. Returns the same interval with whole days carried out of its time and whole months carried out of its days, counting 24 hours to a day and 30 days to a month.

Syntax

normalized_interval(i)

Argument	Type	Description
`i`	`INTERVAL`	The interval to normalize. A string is converted to an `INTERVAL`.

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

A table can't have an INTERVAL column, so the examples build intervals from two timestamps: closed - opened is an interval of days and time. This block connects, defines the run() and plan() helpers, and creates a table of five support tickets. 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_normalized_interval"'

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}.tickets"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, opened TIMESTAMP, closed TIMESTAMP)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, TIMESTAMP '2026-01-01 00:00:00', TIMESTAMP '2026-02-10 06:00:00'), "
    "(2, TIMESTAMP '2026-03-01 08:00:00', TIMESTAMP '2026-03-02 20:00:00'), "
    "(3, TIMESTAMP '2026-03-01 08:00:00', TIMESTAMP '2026-03-01 08:00:59'), "
    "(4, NULL, NULL), "
    "(5, TIMESTAMP '2026-03-10 00:00:00', TIMESTAMP '2026-01-29 00:00:00')")

[]
[]
[(5,)]

What it does

An interval has three fields: months, days, and a time (stored in nanoseconds). Arithmetic can leave more than a day in the time field or more than a month in the days field: the difference of two timestamps, for example, is in days and time and never has months. normalized_interval carries the excess over, counting 24 hours to a day and 30 days to a month:

INTERVAL '40 days 30 hours' becomes 1 month 11 days 6 hours.
INTERVAL '1 year 400 days' becomes 25 months 10 days (a year is 12 months).
INTERVAL '29 days 24 hours' becomes 1 month.

Fields with opposite signs are combined: INTERVAL '1 month -45 days' becomes -15 days, and INTERVAL '-1 day 25 hours' becomes 1 hour. A negative interval stays negative in every field: INTERVAL '-40 days' becomes -1 month -10 days.

The interval keeps its length as the Query Engine measures it: normalized_interval(INTERVAL 1 MONTH) equals normalized_interval(INTERVAL 30 DAY). Normalizing changes what the part functions read from it: hour(INTERVAL '36 hours') is 36, and hour(normalized_interval(INTERVAL '36 hours')) is 12.

NULLs and edge cases

Input	Result
`NULL`	`NULL`
`INTERVAL 59 SECOND`	Unchanged: 59 seconds
`INTERVAL '1000000 hours'`	1388 months 26 days 16 hours
`'40 days'` (a string)	1 month 10 days
`'abc'`	Error: `Could not convert string 'abc' to INTERVAL`
A `DATE`, `TIMESTAMP`, `TIME` or number	Error: `No function matches the given name and argument types`, for example `'normalized_interval(DATE)'`

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

run("SELECT normalized_interval(INTERVAL '40 days 30 hours'), normalized_interval(INTERVAL '1 year 400 days'), "
    "normalized_interval(INTERVAL '29 days 24 hours')")
run("SELECT normalized_interval(INTERVAL '1 month -45 days'), normalized_interval(INTERVAL '-1 day 25 hours'), "
    "normalized_interval(INTERVAL '-40 days')")
run("SELECT normalized_interval(INTERVAL 1 MONTH) = normalized_interval(INTERVAL 30 DAY), "
    "hour(INTERVAL '36 hours'), hour(normalized_interval(INTERVAL '36 hours'))")
run("SELECT normalized_interval(NULL), normalized_interval(INTERVAL 59 SECOND), "
    "normalized_interval(INTERVAL '1000000 hours'), normalized_interval('40 days')")
run("SELECT normalized_interval('abc')")
run("SELECT normalized_interval(DATE '2026-01-01')")

[(MonthDayNano(months=1, days=11, nanoseconds=21600000000000), MonthDayNano(months=25, days=10, nanoseconds=0), MonthDayNano(months=1, days=0, nanoseconds=0))]
[(MonthDayNano(months=0, days=-15, nanoseconds=0), MonthDayNano(months=0, days=0, nanoseconds=3600000000000), MonthDayNano(months=-1, days=-10, nanoseconds=0))]
[(True, 36, 12)]
[(None, MonthDayNano(months=0, days=0, nanoseconds=59000000000), MonthDayNano(months=1388, days=26, nanoseconds=57600000000000), MonthDayNano(months=1, days=10, nanoseconds=0))]
OSError: Could not convert string 'abc' to INTERVAL
InternalError: Binder Error: No function matches the given name and argument types 'normalized_interval(DATE)'. You might need to add explicit type casts.

Examples

How long each ticket stayed open, as the difference of the timestamps and normalized. Ticket 1 was open 40 days 6 hours, which normalizes to 1 month 10 days 6 hours; ticket 5 was closed before it was opened, so its interval is negative:

run(f"SELECT id, closed - opened, normalized_interval(closed - opened) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, MonthDayNano(months=0, days=40, nanoseconds=21600000000000), MonthDayNano(months=1, days=10, nanoseconds=21600000000000)), (2, MonthDayNano(months=0, days=1, nanoseconds=43200000000000), MonthDayNano(months=0, days=1, nanoseconds=43200000000000)), (3, MonthDayNano(months=0, days=0, nanoseconds=59000000000), MonthDayNano(months=0, days=0, nanoseconds=59000000000)), (4, None, None), (5, MonthDayNano(months=0, days=-40, nanoseconds=0), MonthDayNano(months=-1, days=-10, nanoseconds=0))]

The months, days and hours of each normalized interval, with month, day and hour:

run(f"SELECT id, month(normalized_interval(closed - opened)), day(normalized_interval(closed - opened)), "
    f"hour(normalized_interval(closed - opened)) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 1, 10, 6), (2, 0, 1, 12), (3, 0, 0, 0), (4, None, None, None), (5, -1, -10, 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, but not a function of columns, so normalized_interval() is always computed by the Query Engine:

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

The plan of a filter on the normalized interval shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE month(normalized_interval(closed - opened)) >= 1")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│          ("month"         │
│   (normalized_interval(   │
│ (closed - opened))) >= 1) │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           closed          │
│           opened          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

Don't normalize to compare. Intervals compare by length, with 30 days to a month and 24 hours to a day, so INTERVAL '40 days 30 hours' = INTERVAL '1 month 11 days 6 hours' is already true. Write closed - opened > INTERVAL 30 DAY rather than a test on the normalized fields.
Normalize in the SELECT list, where it doesn't stop a filter from being pushed down.
Store the duration if you filter on it often. Compute it when you write the rows, for example as whole seconds in a BIGINT column: a comparison of that column with a constant is pushed down to VAST DataBase.

run("SELECT INTERVAL '40 days 30 hours' = INTERVAL '1 month 11 days 6 hours'")
run(f"SELECT id FROM {TABLE} WHERE closed - opened > INTERVAL 30 DAY ORDER BY id LIMIT 10")

[(True,)]
[(1,)]

Limitations and errors

A month is 30 days. normalized_interval doesn't know which months the interval spans: 40 days is always 1 month 10 days, whether it ran through February or through July.
Intervals only. A date, timestamp, time or number fails with No function matches the given name and argument types. A string must be a valid interval: 'abc' fails with Could not convert string 'abc' to INTERVAL.
Other SQL dialects' names aren't available. justify_interval fails with Scalar Function with name justify_interval does not exist!; use normalized_interval.

run("SELECT justify_interval(INTERVAL '40 days')")

InternalError: Catalog Error: Scalar Function with name justify_interval does not exist!

Similar functions

Function	Does	Result for `INTERVAL '40 days 30 hours'`
`normalized_interval`	Carries hours into days and days into months	1 month 11 days 6 hours
date_trunc with `'day'`	Drops the time field, without carrying it into days	40 days
epoch	The whole length in seconds, as a `DOUBLE`	`3564000.0`

run("SELECT normalized_interval(INTERVAL '40 days 30 hours'), date_trunc('day', INTERVAL '40 days 30 hours'), "
    "epoch(INTERVAL '40 days 30 hours')")

[(MonthDayNano(months=1, days=11, nanoseconds=21600000000000), MonthDayNano(months=0, days=40, nanoseconds=0), 3564000.0)]

Clean up:

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

[]
[]