SQL reference
Date and time formats
Percent format specifiers for strftime, strptime and try_strptime.
strftime formats temporal values. strptime parses text and fails on invalid input; try_strptime returns NULL instead. A list of formats can be tried in order where the overload accepts it.
SELECT strftime(ordered_at, '%Y-%m-%d %H:%M:%S');
SELECT try_strptime(raw_time, ['%Y-%m-%dT%H:%M:%S%z', '%Y-%m-%d']);Specifiers
| Specifier | Meaning |
|---|---|
%a / %A | Abbreviated / full weekday name |
%b / %B | Abbreviated / full month name |
%c | Locale-style date and time representation |
%d / %-d | Zero-padded / unpadded day of month |
%f | Fractional seconds in microseconds |
%g / %G | Two-digit / four-digit ISO week-numbering year |
%H / %-H | Zero-padded / unpadded 24-hour clock hour |
%I / %-I | Zero-padded / unpadded 12-hour clock hour |
%j / %-j | Zero-padded / unpadded day of year |
%m / %-m | Zero-padded / unpadded month number |
%M / %-M | Zero-padded / unpadded minute |
%n | Nanoseconds where the input/parse target supports them |
%p | AM or PM marker |
%S / %-S | Zero-padded / unpadded second |
%u | ISO weekday number, Monday = 1 |
%U | Week number with Sunday as first weekday |
%V | ISO week number |
%w | Weekday number, Sunday = 0 |
%W | Week number with Monday as first weekday |
%x | Locale-style date representation |
%X | Locale-style time representation |
%y / %-y | Two-digit year |
%Y | Four-digit year |
%z | Numeric UTC offset |
%Z | Time-zone name where available |
%% | Literal percent sign |
Parsing guidance
- Prefer ISO 8601 input with an explicit offset for instants.
- Use
TIMESTAMPTZwhen parsed text represents an absolute instant andTIMESTAMPfor local civil time. - A two-digit year is ambiguous across centuries; avoid it for durable data.
- Locale-style formats are not a stable interchange contract.
- Use
try_strptimeduring staged ingestion, retain the original text and review rejected rows. - Formatting a timestamp without a time zone does not create an instant; the missing zone remains missing.