strftime, strptime (date and time function)

Formats timestamps as strings and parses strings as timestamps.

Syntax

strftime(<timestamp>, format => <format> [, timezone => <timezone>])
strptime(<string>, format => <format>)

Arguments

Argument	Type	Description
`<timestamp>`	`timestamp`	Timestamp to format with `strftime`.
`<string>`	`string`	Text to parse with `strptime`.
`format`	`string`	Constant. strftime-style format string. Must not be NULL.
`timezone`	`string`	Optional constant; defaults to `'UTC'`. Time zone for `strftime`, such as an IANA name or a fixed offset. Must be valid and non-NULL.

Returns

strftime returns string.
strptime returns timestamp. If the parsed text has no offset or time zone, it is treated as UTC.

Both return NULL for a NULL input value. Invalid input or formatting raises an error; wrap the call in TRY(...) to handle evaluation errors.

Usage notes

Common format directives include %Y (year), %m (month), %d (day), %H (hour), %M (minute), %S (second), and %z (UTC offset).

Examples

Normalize event times with offsets

Parse producer timestamps with explicit offsets and format them in UTC.

VALUES
    ('web', '2026-06-01 10:00:00 +0000'),
    ('store', '2026-06-01 18:00:00 +0800')
SELECT $0 AS source, strptime($1, format => '%Y-%m-%d %H:%M:%S %z') AS event_time
SELECT
    source,
    event_time,
    strftime(event_time, format => '%Y-%m-%d %H:%M:%S', timezone => 'UTC') AS utc_label

+--------+----------------------+---------------------+
| source | event_time           | utc_label           |
+--------+----------------------+---------------------+
| web    | 2026-06-01T10:00:00Z | 2026-06-01 10:00:00 |
| store  | 2026-06-01T10:00:00Z | 2026-06-01 10:00:00 |
+--------+----------------------+---------------------+

Both source strings represent the same instant, so they can be compared or grouped together.

Label a report in the store time zone

Format one UTC instant in the time zone used by a store report.

SELECT strftime(
    '2026-06-01T10:00:00Z'::timestamp,
    format => '%Y-%m-%d %H:%M:%S %Z',
    timezone => 'Asia/Shanghai'
) AS store_time

+-------------------------+
| store_time              |
+-------------------------+
| 2026-06-01 18:00:00 CST |
+-------------------------+

Formatting changes the presentation; it does not change the stored instant.