strptime

Applies to VAST Cluster 5.5.

Functions › Date and time functions › Parsing and formatting. Parses text into a timestamp, using a format made of % codes such as '%d/%m/%Y', or a list of formats to try in turn. Text that doesn't match stops the query with an error; try_strptime returns NULL instead.

Syntax

strptime(text, format)
strptime(text, [format, ...])

Returns: TIMESTAMP. With %z in the format (in any format of a list), TIMESTAMP WITH TIME ZONE, converted to UTC. With %n, TIMESTAMP_NS.

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_strptime. 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 imported rows. Each has a date as text (s), in whatever form it arrived, and a load time (ts). 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_strptime"'

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}.imports"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, s VARCHAR, ts TIMESTAMP)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '2026-02-28', TIMESTAMP '2026-02-28 23:15:00'), "
    "(2, '01/03/2026', TIMESTAMP '2026-03-01 08:30:45.123456'), "
    "(3, 'not a date', TIMESTAMP '2024-12-30 12:00:00'), "
    "(4, NULL, NULL), "
    "(5, '', TIMESTAMP '2024-02-29 00:00:00'), "
    "(6, '2026-02-30', TIMESTAMP '1969-12-31 23:59:59.999999'), "
    "(7, ' 2026-02-28 ', TIMESTAMP '2027-01-03 00:00:00.5')")

[]
[]
[(7,)]

What it does

strptime reads the text from left to right against the format. A % code reads one field; any other character must appear in the text as written. The parts the format doesn't name take their first value: a format with no date gives 1 January 1900, and one with no time gives midnight.

%-m, %-d and the other %- forms are accepted too. Spaces before and after the text are skipped.

NULLs and edge cases

The same cases, as the cluster returns them. They use the table and helpers from Set up the examples:

run("SELECT strptime(NULL, '%Y-%m-%d'), strptime('2024-02-29', NULL), strptime('2024-02-29', NULL::VARCHAR[])")
run("SELECT strptime('366-2023', '%j-%Y'), strptime('Mon 29 Feb 2024', '%a %d %b %Y'), "
    "strptime('2024-02-29 13:45:30.5', '%Y-%m-%d %H:%M:%S.%f'), "
    "strptime('2024-02-29 13:45:30 Europe/Lisbon', '%Y-%m-%d %H:%M:%S %Z')")
run("SELECT strptime(' 2024-02-29 ', '%Y-%m-%d'), strptime('2024-2-9', '%Y-%m-%d'), strptime('1:45 PM', '%I:%M %p'), "
    "strptime('69-01-01', '%y-%m-%d'), strptime('68-01-01', '%y-%m-%d'), strptime('2024W09', '%GW%V')")
run("SELECT strptime('2023-02-29', '%Y-%m-%d')")
run("SELECT strptime('13:00 PM', '%I:%M %p')")
run("SELECT strptime('2024-02-29 13:45:30Z', '%Y-%m-%d %H:%M:%S%z')")
run("SELECT strptime('', '%Y-%m-%d')")
run("SELECT strptime('2024-02-29', '%Y-%m')")
run("SELECT strptime('10000-01-01', '%Y-%m-%d')")
run("SELECT strptime(DATE '2024-02-29', '%Y-%m-%d')")

[(None, None, None)]
[(datetime.datetime(2024, 1, 1, 0, 0), datetime.datetime(2024, 2, 29, 0, 0), datetime.datetime(2024, 2, 29, 13, 45, 30, 5), datetime.datetime(2024, 2, 29, 13, 45, 30))]
[(datetime.datetime(2024, 2, 29, 0, 0), datetime.datetime(2024, 2, 9, 0, 0), datetime.datetime(1900, 1, 1, 13, 45), datetime.datetime(1969, 1, 1, 0, 0), datetime.datetime(2068, 1, 1, 0, 0), datetime.datetime(2024, 2, 26, 0, 0))]
OSError: Date out of range: 2023-2-29
OSError: Could not parse string "13:00 PM" according to format specifier "%I:%M %p"
OSError: Could not parse string "2024-02-29 13:45:30Z" according to format specifier "%Y-%m-%d %H:%M:%S%z"
OSError: Could not parse string "" according to format specifier "%Y-%m-%d"
OSError: Could not parse string "2024-02-29" according to format specifier "%Y-%m"
OSError: Could not parse string "10000-01-01" according to format specifier "%Y-%m-%d"
InternalError: Binder Error: No function matches the given name and argument types 'strptime(DATE, STRING_LITERAL)'. You might need to add explicit type casts.

Examples

Parse the first row's text, which is in YYYY-MM-DD form:

run(f"SELECT id, s, strptime(s, '%Y-%m-%d') FROM {TABLE} WHERE id = 1")

[(1, '2026-02-28', datetime.datetime(2026, 2, 28, 0, 0))]

The first two rows hold dates in two forms. Give both formats in a list:

run(f"SELECT id, s, strptime(s, ['%Y-%m-%d', '%d/%m/%Y']) FROM {TABLE} WHERE id <= 2 ORDER BY id LIMIT 10")

[(1, '2026-02-28', datetime.datetime(2026, 2, 28, 0, 0)), (2, '01/03/2026', datetime.datetime(2026, 3, 1, 0, 0))]

The order of the list decides text that matches more than one format. 01/02/2024 is 1 February with the day first, and 2 January with the month first:

run("SELECT strptime('01/02/2024', ['%d/%m/%Y', '%m/%d/%Y']), strptime('01/02/2024', ['%m/%d/%Y', '%d/%m/%Y'])")

[(datetime.datetime(2024, 2, 1, 0, 0), datetime.datetime(2024, 1, 2, 0, 0))]

A date and time with a UTC offset. With %z, the result is a TIMESTAMP WITH TIME ZONE in UTC, which the ADBC driver returns as a datetime in UTC: 13:45:30 at +02:30 is 11:15:30 UTC:

run("SELECT strptime('2024-02-29 13:45:30+02:30', '%Y-%m-%d %H:%M:%S%z'), "
    "strptime('2024-02-29T13:45:30.123456', '%Y-%m-%dT%H:%M:%S.%f')")

[(datetime.datetime(2024, 2, 29, 11, 15, 30, tzinfo=zoneinfo.ZoneInfo(key='UTC')), datetime.datetime(2024, 2, 29, 13, 45, 30, 123456))]

With %n, the result has nanoseconds, which Python's datetime can't hold, so cast it to VARCHAR to read it:

run("SELECT CAST(strptime('2024-02-29 13:45:30.123456789', '%Y-%m-%d %H:%M:%S.%n') AS VARCHAR)")

[('2024-02-29 13:45:30.123456789',)]

On the whole column, the query stops at the first text that doesn't match. Use try_strptime to get NULL for those rows instead:

run(f"SELECT id, strptime(s, '%Y-%m-%d') FROM {TABLE} ORDER BY id LIMIT 10")

OSError: Could not parse string "01/03/2026" according to format specifier "%Y-%m-%d"

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 strptime() of a column is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine parses the text of each row it returns. A condition that VAST DataBase evaluates, such as id = 1 above, runs first, so strptime() never sees the rows it removes.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine parses the text and keeps the rows that match. No rows are skipped in storage.

The plan of a condition on strptime(s, ...) shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE strptime(s, '%Y-%m-%d') > TIMESTAMP '2026-01-01 00:00:00'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (strptime(s, '%Y-%m-%d') >│
│   '2026-01-01 00:00:00':  │
│        :TIMESTAMP)        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             s             │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Parse constants, not columns. strptime() of a constant is computed once, before the scan, so ts > strptime('2026-03-01', '%Y-%m-%d') is a comparison of the raw column with a constant and is pushed down to VAST DataBase. The plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE ts > strptime('2026-03-01', '%Y-%m-%d')")

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

• Store timestamps as TIMESTAMP, not as text. Parse the text once, when you load it, and keep the result in a TIMESTAMP column. A filter on that column with a constant is pushed down; a filter on strptime(s, ...) parses every row in the Query Engine.

Limitations and errors

• The format must be a constant. A format taken from a column fails with Invalid Input Error: strptime format must be a constant. An empty list fails with strptime format must be a string, and an empty format with Failed to parse format specifier : Empty format string.
• Not every % code. %s (epoch seconds), %F and others fail with Invalid Input Error: Failed to parse format specifier %s: Unrecognized format for strftime/strptime: %s. A list that contains such a code fails even if another format would match. For epoch seconds, use to_timestamp; for %F, write %Y-%m-%d.
• %f reads a number of microseconds. .5 gives 5 microseconds and .1234567 fails. When the text can have fewer than six digits after the point, pad it first, or use %g for exactly three.
• English names and ASCII digits only. A month name in another language, or digits from another script, fails with Could not parse string.
• Time-zone names aren't applied. %Z reads a name and ignores it; only %z offsets are converted to UTC. Z for UTC isn't accepted by %z. See time zones.
• The error shows only the first format of a list. When no format of a list matches, the error names the first one, for example according to format specifier "%Y".

run(f"SELECT id, strptime(s, s) FROM {TABLE} WHERE id = 1")
run("SELECT strptime('1709214330', '%s')")
run("SELECT strptime('2024-02-29', ['%Q', '%Y-%m-%d'])")
run("SELECT strptime('F\u00e9vrier 2024', '%B %Y')")
run("SELECT strptime('x', ['%Y', '%d/%m/%Y'])")

InternalError: Invalid Input Error: strptime format must be a constant
InternalError: Invalid Input Error: Failed to parse format specifier %s: Unrecognized format for strftime/strptime: %s
InternalError: Invalid Input Error: Failed to parse format specifier %Q: Unrecognized format for strftime/strptime: %Q
OSError: Could not parse string "Février 2024" according to format specifier "%B %Y"
OSError: Could not parse string "x" according to format specifier "%Y"

Similar functions

run("SELECT strptime('2024-02-29', '%Y-%m-%d'), CAST('2024-02-29' AS TIMESTAMP), CAST('2024-02-29' AS DATE)")
run("SELECT try_strptime('2023-02-29', '%Y-%m-%d'), TRY_CAST('2023-02-29' AS DATE), TRY_CAST('29/02/2024' AS DATE)")

[(datetime.datetime(2024, 2, 29, 0, 0), datetime.datetime(2024, 2, 29, 0, 0), datetime.date(2024, 2, 29))]
[(None, None, None)]

Clean up:

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

[]
[]

See also

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