monthname

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Names and calendar. Returns the English name of the month of a date or timestamp, such as March.

Syntax

monthname(value)

Returns: VARCHAR.

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_monthname. 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 seven events. Each has a date (d), a timestamp (ts), a time of day (t) and a timestamp with nanoseconds (tns). Row 4 has no values, and row 6 is the last moment of 1969:

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

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, d DATE, ts TIMESTAMP, t TIME, tns TIMESTAMP_NS)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, DATE '2026-02-28', TIMESTAMP '2026-02-28 23:15:00', TIME '23:15:00', "
    "CAST('2026-02-28 23:15:00.123456789' AS TIMESTAMP_NS)), "
    "(2, DATE '2026-03-01', TIMESTAMP '2026-03-01 08:30:45.123456', TIME '08:30:45.123456', "
    "CAST('2026-03-01 08:30:45.123456' AS TIMESTAMP_NS)), "
    "(3, DATE '2024-12-30', TIMESTAMP '2024-12-30 12:00:00', TIME '12:00:00', "
    "CAST('2024-12-30 12:00:00' AS TIMESTAMP_NS)), "
    "(4, NULL, NULL, NULL, NULL), "
    "(5, DATE '2024-02-29', TIMESTAMP '2024-02-29 00:00:00', TIME '00:00:00', "
    "CAST('2024-02-29 00:00:00' AS TIMESTAMP_NS)), "
    "(6, DATE '1969-12-31', TIMESTAMP '1969-12-31 23:59:59.999999', TIME '23:59:59.999999', "
    "CAST('1969-12-31 23:59:59.999999999' AS TIMESTAMP_NS)), "
    "(7, DATE '2027-01-03', TIMESTAMP '2027-01-03 00:00:00.5', TIME '00:00:00.000001', "
    "CAST('2027-01-03 00:00:00.5' AS TIMESTAMP_NS))")

[]
[]
[(7,)]

What it does

monthname returns the full English name of the month, starting with a capital letter, from January to December. For example, monthname(DATE '2026-01-31') is 'January' and monthname(DATE '2026-12-01') is 'December'. It takes no language argument.

For the short name (Mar), use the %b code of strftime. For the month as a number, use month. Names don't sort in calendar order (April comes first), so sort by month(d).

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, monthname(ts) FROM {TABLE} WHERE id = 4")
run("SELECT monthname(NULL), monthname(DATE 'infinity'), monthname(TIMESTAMP '-infinity')")
run("SELECT monthname(DATE '0001-01-01'), monthname(DATE '9999-12-31'), "
    "monthname(CAST('0044-03-15 (BC)' AS DATE)), monthname(TIMESTAMP '1969-12-31 23:59:59.999999')")
run("SELECT monthname(INTERVAL '1 year 2 months 3 days')")
run("SELECT monthname(TIME '13:45:30')")
run("SELECT monthname('2026-03-01')")
run("SELECT monthname(123)")

[(4, None)]
[(None, None, None)]
[('January', 'December', 'March', 'December')]
InternalError: Binder Error: No function matches the given name and argument types 'monthname(INTERVAL)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'monthname(TIME)'. You might need to add explicit type casts.
InternalError: Binder Error: Could not choose a best candidate function for the function call "monthname(STRING_LITERAL)". In order to select one, please add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'monthname(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The name of the month of each event:

run(f"SELECT id, d, monthname(d), monthname(ts) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, datetime.date(2026, 2, 28), 'February', 'February'), (2, datetime.date(2026, 3, 1), 'March', 'March'), (3, datetime.date(2024, 12, 30), 'December', 'December'), (4, None, None, None), (5, datetime.date(2024, 2, 29), 'February', 'February'), (6, datetime.date(1969, 12, 31), 'December', 'December'), (7, datetime.date(2027, 1, 3), 'January', 'January')]

A label such as February 2026, built from monthname and year:

run(f"SELECT id, monthname(d) || ' ' || CAST(year(d) AS VARCHAR) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'February 2026'), (2, 'March 2026'), (3, 'December 2024'), (4, None), (5, 'February 2024'), (6, 'December 1969'), (7, 'January 2027')]

Keep only the events in February of any year:

run(f"SELECT id FROM {TABLE} WHERE monthname(d) = 'February' ORDER BY id LIMIT 10")

[(1,), (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 a column, so monthname() is always computed by the Query Engine:

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

The plan of the February example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE monthname(d) = 'February'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│(monthname(d) = 'February')│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             d             │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• For one month of one year, filter on a range of the raw column. d >= DATE '2026-02-01' AND d < DATE '2026-03-01' is February 2026, and it's pushed down to VAST DataBase, so rows outside it never leave storage. The plan for the range has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE d >= DATE '2026-02-01' AND d < DATE '2026-03-01'")

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

• Store the month if you filter on it across years. February of every year has no single range. Compute the month when you write the rows and keep it in its own column: event_month = 2 is pushed down, monthname(d) = 'February' isn't.

Limitations and errors

• English names only. monthname takes one argument; there is no way to choose the language. Map the names yourself, or map the month number, for other languages.
• No time-zone support. On a TIMESTAMP WITH TIME ZONE value, such as now(), monthname fails with an Extension Autoloading Error. Cast the value to TIMESTAMP first; see time zones.
• Not for TIME or INTERVAL. Both fail with No function matches the given name and argument types.
• TIMESTAMP_NS before 1970. Row 6's TIMESTAMP_NS value 1969-12-31 23:59:59.999999999 gives 'January', while the TIMESTAMP value 1969-12-31 23:59:59.999999 gives 'December'. If you need the month of nanosecond values before 1970 that are close to midnight at a month end, check the result or store the value as TIMESTAMP.

run(f"SELECT id, monthname(tns), monthname(ts) FROM {TABLE} WHERE id = 6")

[(6, 'January', 'December')]

Similar functions

run("SELECT monthname(DATE '2026-03-01'), strftime(DATE '2026-03-01', '%B'), strftime(DATE '2026-03-01', '%b'), "
    "month(DATE '2026-03-01'), dayname(DATE '2026-03-01')")

[('March', 'March', 'Mar', 3, 'Sunday')]

Clean up:

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

[]
[]

See also

• dayname: the name of the day of the week.
• month: the month as a number.
• Date and time functions: all the date and time functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.