week

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Weeks and days of the week. Returns the ISO 8601 week number of a date or timestamp, from 1 to 53. ISO weeks start on Monday, and week 1 is the week that contains the year's first Thursday. weekofyear is the same function.

Syntax

week(value)
weekofyear(value)

Returns: BIGINT.

Aliases: weekofyear.

Same as: date_part('week', value) and extract(week FROM value); the part also has the names w, weeks and weekofyear. 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_week. 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 six events. Each has a date (d) and a timestamp (ts). Rows 3, 5 and 6 are around New Year, where the ISO week and the calendar year disagree, 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_week"'

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)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, DATE '2026-02-28', TIMESTAMP '2026-02-28 23:15:00'), "
    "(2, DATE '2026-03-01', TIMESTAMP '2026-03-01 08:30:45'), "
    "(3, DATE '2024-12-30', TIMESTAMP '2024-12-30 12:00:00'), "
    "(4, NULL, NULL), "
    "(5, DATE '2027-01-03', TIMESTAMP '2027-01-03 00:00:00'), "
    "(6, DATE '2027-01-04', TIMESTAMP '2027-01-04 09:00:00')")

[]
[]
[(6,)]

What it does

week returns the ISO 8601 week of a date or timestamp. ISO weeks run from Monday to Sunday, and week 1 of a year is the week that holds its first Thursday. So a year has 52 or 53 weeks, and the first and last days of a calendar year can belong to a week of the next or the previous year:

The same dates, as the cluster returns them:

for day in ["2024-12-29", "2024-12-30", "2021-01-01", "2027-01-03", "2027-01-04"]:
    run(f"SELECT DATE '{day}', dayname(DATE '{day}'), week(DATE '{day}'), isoyear(DATE '{day}'), year(DATE '{day}')")

[(datetime.date(2024, 12, 29), 'Sunday', 52, 2024, 2024)]
[(datetime.date(2024, 12, 30), 'Monday', 1, 2025, 2024)]
[(datetime.date(2021, 1, 1), 'Friday', 53, 2020, 2021)]
[(datetime.date(2027, 1, 3), 'Sunday', 53, 2026, 2027)]
[(datetime.date(2027, 1, 4), 'Monday', 1, 2027, 2027)]

week doesn't say which year the week belongs to. To label a week, use yearweek, which gives 202501 for 30 December 2024, or isoyear with week, not year with week.

It works on every timestamp precision. weekofyear is the same function under another name.

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, week(d), weekofyear(ts) FROM {TABLE} WHERE id = 4")
run("SELECT week(DATE 'infinity'), week(TIMESTAMP '-infinity'), week(DATE '0001-01-01'), "
    "week(DATE '9999-12-31'), week(TIMESTAMP '1969-12-31 23:59:59.999999')")
run("SELECT week(DATE '2024-12-29'), week(DATE '2024-12-30'), week(DATE '2021-01-01'), "
    "week(DATE '2027-01-03'), week(DATE '2027-01-04'), weekofyear(DATE '2021-01-01')")
run("SELECT week(NULL)")
run("SELECT week(INTERVAL 1 DAY)")
run("SELECT week(TIME '13:45:30')")
run("SELECT week('2024-02-29')")
run("SELECT week(123)")

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

Examples

The week of each event, next to its calendar year and its ISO year and week. Row 3 is in week 1 of 2025, and row 5 in week 53 of 2026:

run(f"SELECT id, d, week(d), weekofyear(ts), year(d), yearweek(d) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, datetime.date(2026, 2, 28), 9, 9, 2026, 202609), (2, datetime.date(2026, 3, 1), 9, 9, 2026, 202609), (3, datetime.date(2024, 12, 30), 1, 1, 2024, 202501), (4, None, None, None, None, None), (5, datetime.date(2027, 1, 3), 53, 53, 2027, 202653), (6, datetime.date(2027, 1, 4), 1, 1, 2027, 202701)]

Keep only the events in week 9. Without a year, this matches week 9 of every year. Make it faster has a faster way to write it for one year:

run(f"SELECT id FROM {TABLE} WHERE week(d) = 9 ORDER BY id LIMIT 10")

[(1,), (2,)]

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

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

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│      ("week"(d) = 9)      │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             d             │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Filter on a range of the raw column for one week. ISO week 9 of 2026 runs from Monday 23 February to Sunday 1 March 2026. date_trunc with 'week' gives the Monday: date_trunc('week', DATE '2026-03-01') is 2026-02-23. d >= DATE '2026-02-23' AND d < DATE '2026-03-02' is pushed down to VAST DataBase, so rows from other weeks never leave storage. The plan for the range has no FILTER step:

run("SELECT date_trunc('week', DATE '2026-03-01')")
run(f"SELECT id FROM {TABLE} WHERE d >= DATE '2026-02-23' AND d < DATE '2026-03-02' ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE d >= DATE '2026-02-23' AND d < DATE '2026-03-02'")

[(datetime.date(2026, 2, 23),)]
[(1,), (2,)]
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

• Store the week if you filter on it often and a range doesn't fit your query, for example week 9 of every year. Compute it with yearweek or week when you write the rows and keep it in its own column: event_week = 9 is pushed down, week(d) = 9 isn't.

Limitations and errors

• ISO weeks only. week has no option for weeks that start on Sunday, or for week 1 starting on 1 January. For those numberings, use strftime with '%U' (weeks starting on Sunday) or '%W' (weeks starting on Monday), which return text; see Similar functions.
• Dates and timestamps only. week of an INTERVAL fails with interval units "week" not recognized, although the error for a wrong argument lists week(INTERVAL).
• No time-zone support. On a TIMESTAMP WITH TIME ZONE value, such as now(), week fails with an Extension Autoloading Error. Cast the value to TIMESTAMP first; see time zones.
• Other SQL dialects' names aren't available. week_of_year fails with Scalar Function with name week_of_year does not exist!. Use week or weekofyear.

run("SELECT week_of_year(DATE '2026-03-01')")

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

Similar functions

run("SELECT week(TIMESTAMP '2024-12-30 13:45:30.123456'), weekofyear(TIMESTAMP '2024-12-30 13:45:30.123456'), "
    "yearweek(TIMESTAMP '2024-12-30 13:45:30.123456'), isoyear(TIMESTAMP '2024-12-30 13:45:30.123456'), "
    "year(TIMESTAMP '2024-12-30 13:45:30.123456'), strftime(TIMESTAMP '2024-12-30 13:45:30.123456', '%V %G %U %W'), "
    "date_part('week', TIMESTAMP '2024-12-30 13:45:30.123456')")

[(1, 1, 202501, 2025, 2024, '01 2025 52 53', 1)]

Clean up:

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

[]
[]

See also

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