strftime

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Parsing and formatting. Formats a date or timestamp as text, using a format made of % codes such as '%d/%m/%Y %H:%M'. It's the reverse of strptime.

Syntax

strftime(value, format)
strftime(format, value)

Returns: VARCHAR.

The arguments can come in either order: strftime('%Y-%m-%d', ts) is the same as strftime(ts, '%Y-%m-%d').

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_strftime. 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 five events. Each has a date (d), a timestamp (ts) and a timestamp with nanoseconds (tns). Row 4 has no values, and row 5 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_strftime"'

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

[]
[]
[(5,)]

What it does

strftime writes the format out with each % code replaced by a part of the value. Any other text is copied as it is, including non-ASCII text. The codes, with the result for TIMESTAMP '2024-02-29 13:45:30.123456' (a Thursday):

On a DATE, the time codes give zeros: %H:%M:%S is 00:00:00 and %p is AM. %n gives all nine digits only on a TIMESTAMP_NS; a TIMESTAMP has microseconds, so %n ends in 000.

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, strftime(ts, '%Y-%m-%d'), strftime(d, NULL) FROM {TABLE} WHERE id = 4")
run("SELECT strftime(DATE 'infinity', '%Y-%m-%d'), strftime(TIMESTAMP '-infinity', '%Y'), "
    "strftime(CAST('10000-01-01' AS DATE), '%Y'), strftime(TIMESTAMP '2024-02-29 00:00:00', '%I %p'), "
    "strftime(TIMESTAMP '2024-02-29 12:00:00', '%I %p'), strftime(DATE '2024-02-29', 'no codes'), "
    "strftime(DATE '2024-02-29', '%%Y')")
run("SELECT strftime(DATE '2024-02-29', '')")
run("SELECT strftime(DATE '2024-02-29', '%Y-%m-%d %')")
run("SELECT strftime(DATE '2024-02-29', '%Y-%Q')")
run("SELECT strftime('2024-02-29', '%Y')")
run("SELECT strftime(TIME '13:45:30', '%H')")

[(4, None, None)]
[('infinity', '-infinity', '10000', '12 AM', '12 PM', 'no codes', '%Y')]
InternalError: Invalid Input Error: Failed to parse format specifier : Empty format string
InternalError: Invalid Input Error: Failed to parse format specifier %Y-%m-%d %: Trailing format character %
InternalError: Invalid Input Error: Failed to parse format specifier %Y-%Q: Unrecognized format for strftime/strptime: %Q
InternalError: Binder Error: Could not choose a best candidate function for the function call "strftime(STRING_LITERAL, STRING_LITERAL)". In order to select one, please add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'strftime(TIME, STRING_LITERAL)'. You might need to add explicit type casts.

Examples

Each event's timestamp in day-first form, and its date written out:

run(f"SELECT id, strftime(ts, '%d/%m/%Y %H:%M:%S.%g'), strftime(d, '%A %d %B %Y') FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '28/02/2026 23:15:00.000', 'Saturday 28 February 2026'), (2, '01/03/2026 08:30:45.123', 'Sunday 01 March 2026'), (3, '30/12/2024 12:00:00.000', 'Monday 30 December 2024'), (4, None, None), (5, '31/12/1969 23:59:59.999', 'Wednesday 31 December 1969')]

The nanoseconds of a TIMESTAMP_NS column. Python's datetime can't hold them, so formatting the value as text is also a way to read it:

run(f"SELECT id, strftime(tns, '%Y-%m-%d %H:%M:%S.%n') FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '2026-02-28 23:15:00.123456789'), (2, '2026-03-01 08:30:45.123456000'), (3, '2024-12-30 12:00:00.000000000'), (4, None), (5, '1969-12-31 23:59:59.999999999')]

Literal text around the codes, with the format first:

run("SELECT strftime('Year: %Y, day %j!', TIMESTAMP '2024-02-29 13:45:30'), "
    "strftime(DATE '2024-02-29', 'ann\u00e9e %Y \u00e9 \u65e5')")

[('Year: 2024, day 060!', 'année 2024 é 日')]

Week numbers around the turn of a year. 30 December 2024 is in ISO week 1 of 2025, and 3 January 2027 is in ISO week 53 of 2026:

run("SELECT strftime(DATE '2024-12-30', '%U %W %V %G %u %w'), strftime(DATE '2027-01-03', '%U %W %V %G %u %w')")

[('52 53 01 2025 1 1', '01 00 53 2026 7 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 a column, so strftime() is always computed by the Query Engine:

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

The plan of a filter on the formatted month shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE strftime(ts, '%Y-%m') = '2026-02'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (strftime(ts, '%Y-%m') =  │
│         '2026-02')        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             ts            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Filter on a range of the raw column, not on formatted text. ts >= TIMESTAMP '2026-02-01 00:00:00' AND ts < TIMESTAMP '2026-03-01 00:00:00' returns the same rows as strftime(ts, '%Y-%m') = '2026-02', and it's pushed down to VAST DataBase, so rows that don't match never leave storage. The plan has no FILTER step:

run(f"SELECT id FROM {TABLE} WHERE ts >= TIMESTAMP '2026-02-01 00:00:00' AND ts < TIMESTAMP '2026-03-01 00:00:00' "
    "ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE ts >= TIMESTAMP '2026-02-01 00:00:00' AND ts < TIMESTAMP '2026-03-01 00:00:00'")

[(1,)]
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

• Format in the SELECT list, or in your client. strftime in the SELECT list runs only on the rows the query returns. If your client formats dates anyway, you can return the DATE or TIMESTAMP and format it there.

Limitations and errors

• The format must be a constant. A format taken from a column, or built from one with ||, fails with Invalid Input Error: strftime format must be a constant.
• Not every % code. %e, %C, %D, %F, %R, %r, %s, %k, %l and others fail with Unrecognized format for strftime/strptime. Write %Y-%m-%d for %F, %H:%M for %R, and use epoch for %s.
• No time zones. A TIMESTAMP WITH TIME ZONE, such as now(), fails with No function matches the given name and argument types 'strftime(TIMESTAMP WITH TIME ZONE, STRING_LITERAL)'. Cast it to TIMESTAMP first; the result is in UTC, and %z gives +00. See time zones.
• English names only. %A, %a, %B and %b are always in English. For other languages, map the numbers (%m, %u) to names in your client or with a CASE expression.

run(f"SELECT strftime(ts, '%Y-' || CAST(id AS VARCHAR)) FROM {TABLE} WHERE id = 1")
run("SELECT strftime(DATE '2024-02-29', '%F')")
run("SELECT strftime(now(), '%Y')")
run("SELECT strftime(CAST(TIMESTAMPTZ '2024-02-29 13:45:30+02' AS TIMESTAMP), '%Y-%m-%d %H:%M %z')")

InternalError: Invalid Input Error: strftime format must be a constant
InternalError: Invalid Input Error: Failed to parse format specifier %F: Unrecognized format for strftime/strptime: %F
InternalError: Binder Error: No function matches the given name and argument types 'strftime(TIMESTAMP WITH TIME ZONE, STRING_LITERAL)'. You might need to add explicit type casts.
[('2024-02-29 11:45 +00',)]

Similar functions

run("SELECT strftime(TIMESTAMP '2024-12-30 13:45:30.123456', '%Y-%m-%d %H:%M:%S.%f'), "
    "CAST(TIMESTAMP '2024-12-30 13:45:30.123456' AS VARCHAR), year(TIMESTAMP '2024-12-30 13:45:30.123456'), "
    "strftime(TIMESTAMP '2024-12-30 13:45:30.123456', '%Y')")
run("SELECT dayname(TIMESTAMP '2024-12-30 13:45:30.123456'), monthname(TIMESTAMP '2024-12-30 13:45:30.123456'), "
    "strftime(TIMESTAMP '2024-12-30 13:45:30.123456', '%A %B'), date_trunc('month', TIMESTAMP '2024-12-30 13:45:30.123456'), "
    "strftime(TIMESTAMP '2024-12-30 13:45:30.123456', '%Y-%m-01')")

[('2024-12-30 13:45:30.123456', '2024-12-30 13:45:30.123456', 2024, '2024')]
[('Monday', 'December', 'Monday December', datetime.date(2024, 12, 1), '2024-12-01')]

Clean up:

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

[]
[]

See also

• strptime: parsing text with the same codes.
• Date and time functions: all the date and time functions, and parsing and formatting.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.