dayofyear

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Weeks and days of the week. Returns the day of the year of a date or timestamp, from 1 for 1 January to 365, or 366 in a leap year.

Syntax

dayofyear(value)

Returns: BIGINT.

Same as: date_part('doy', value), also written date_part('dayofyear', value); see date_part.

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_dayofyear. 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, row 5 is a leap day, 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_dayofyear"'

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

dayofyear counts the days from 1 January, which is day 1. After February, the same calendar date has a number one higher in a leap year:

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

[(4, None)]
[(60, 61, 365, 366)]
[(None, None, 1, 365, 365)]
InternalError: Binder Error: Could not choose a best candidate function for the function call "dayofyear("NULL")". In order to select one, please add explicit type casts.
OSError: interval units "doy" not recognized
InternalError: Binder Error: No function matches the given name and argument types 'dayofyear(TIME)'. You might need to add explicit type casts.
InternalError: Binder Error: Could not choose a best candidate function for the function call "dayofyear(STRING_LITERAL)". In order to select one, please add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'dayofyear(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The day of the year of each event. Row 1 (28 February 2026) is day 59, and row 5 (29 February 2024) day 60:

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

[(1, datetime.date(2026, 2, 28), 59, 59), (2, datetime.date(2026, 3, 1), 60, 60), (3, datetime.date(2024, 12, 30), 365, 365), (4, None, None, None), (5, datetime.date(2024, 2, 29), 60, 60), (6, datetime.date(1969, 12, 31), 365, 365), (7, datetime.date(2027, 1, 3), 3, 3)]

Keep only the events in the first 60 days of their year, whatever the year:

run(f"SELECT id, d FROM {TABLE} WHERE dayofyear(d) <= 60 ORDER BY id LIMIT 10")

[(1, datetime.date(2026, 2, 28)), (2, datetime.date(2026, 3, 1)), (5, datetime.date(2024, 2, 29)), (7, datetime.date(2027, 1, 3))]

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

• In the SELECT list, the scan reads the column and the Query Engine computes dayofyear() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes dayofyear() 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, d FROM {TABLE} WHERE dayofyear(d) <= 60")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             id            │
│             d             │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│    (dayofyear(d) <= 60)   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             d             │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• For days of one year, filter on a range of the raw column. The first 60 days of 2026 are d >= DATE '2026-01-01' AND d < DATE '2026-03-02'. The range is 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, d FROM {TABLE} WHERE d >= DATE '2026-01-01' AND d < DATE '2026-03-02'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             id            │
│             d             │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             d             │
│             id            │
│                           │
└───────────────────────────┘

• Store the day of the year if you filter on it across years. Compute it when you write the rows and keep it in its own column: day_of_year <= 60 is pushed down, dayofyear(d) <= 60 isn't.

Limitations and errors

• No time-zone support. On a TIMESTAMP WITH TIME ZONE value, such as now(), dayofyear fails with an Extension Autoloading Error. Cast the value to TIMESTAMP first; see time zones.
• Not for TIME or INTERVAL. The error for an INTERVAL lists dayofyear(INTERVAL) among the candidates, but running it fails with interval units "doy" not recognized.
• TIMESTAMP_NS before 1970. Row 6's TIMESTAMP_NS value 1969-12-31 23:59:59.999999999 gives 1, while the TIMESTAMP value 1969-12-31 23:59:59.999999 gives 365. If you need the day of nanosecond values before 1970 that are close to midnight, check the result or store the value as TIMESTAMP.
• Other SQL dialects' names aren't available. day_of_year and doy fail with Scalar Function with name ... does not exist!. Use dayofyear, or date_part('doy', ...).

run(f"SELECT id, dayofyear(tns), dayofyear(ts) FROM {TABLE} WHERE id = 6")
run("SELECT day_of_year(DATE '2026-03-01')")
run("SELECT doy(DATE '2026-03-01')")

[(6, 1, 365)]
InternalError: Catalog Error: Scalar Function with name day_of_year does not exist!
InternalError: Catalog Error: Scalar Function with name doy does not exist!

Similar functions

run("SELECT dayofyear(DATE '2024-02-29'), day(DATE '2024-02-29'), strftime(DATE '2024-02-29', '%j'), "
    "date_part('doy', DATE '2024-02-29')")

[(60, 29, '060', 60)]

Clean up:

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

[]
[]

See also

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