In DuckDB, we can use functions like strftime() and strptime() to format date or timestamp values. These functions accept a format string that specifies how the date or timestamp should be formatted.
The format string consists of one or more format specifiers. For example, '%d/%m/%Y' is a format string that consists of three format specifiers. The output from that format specifier might look something like 05/09/2050.
DuckDB has a pretty good range of format specifiers that we can use to format our dates and timestamps.
Below is a full list of all available format specifiers in DuckDB:
| Specifier | Description | Example |
|---|---|---|
%a | Abbreviated weekday name. | Sun, Mon, Tue, … |
%A | Full weekday name. | Sunday, Monday, Tuesday, … |
%b | Abbreviated month name. | Jan, Feb, Mar, … |
%B | Full month name. | January, February, March, … |
%c | ISO date and time representation. | 2050-08-07 12:45:16 |
%d | Day of the month as a zero-padded decimal. | 01, 02, 03, … |
%-d | Day of the month as a decimal number. | 1, 2, 3, … |
%f | Microsecond as a decimal number, zero-padded on the left. | 000000 – 999999 |
%g | Millisecond as a decimal number, zero-padded on the left. | 000 – 999 |
%G | ISO 8601 year with century representing the year that contains the greater part of the ISO week (see %V). | 0001, 0002, …, 2024, 2025, …, 9998, 9999 |
%H | Hour (24-hour clock) as a zero-padded decimal number. | 00, 01, …, 23 |
%-H | Hour (24-hour clock) as a decimal number. | 0, 1, …, 23 |
%I | Hour (12-hour clock) as a zero-padded decimal number. | 01, 02, …, 12 |
%-I | Hour (12-hour clock) as a decimal number. | 1, 2, … 12 |
%j | Day of the year as a zero-padded decimal number. | 001, 002, …, 366 |
%-j | Day of the year as a decimal number. | 1, 2, …, 366 |
%m | Month as a zero-padded decimal number. | 01, 02, …, 12 |
%-m | Month as a decimal number. | 1, 2, …, 12 |
%M | Minute as a zero-padded decimal number. | 00, 01, …, 59 |
%-M | Minute as a decimal number. | 0, 1, …, 59 |
%n | Nanosecond as a decimal number, zero-padded on the left. | 000000000 – 999999999 |
%p | Locale’s AM or PM. | AM, PM |
%S | Second as a zero-padded decimal number. | 00, 01, …, 59 |
%-S | Second as a decimal number. | 0, 1, …, 59 |
%u | ISO 8601 weekday as a decimal number where 1 is Monday. | 1, 2, …, 7 |
%U | Week number of the year. Week 01 starts on the first Sunday of the year, so there can be week 00. Note that this is not compliant with the week date standard in ISO-8601. | 00, 01, …, 53 |
%V | ISO 8601 week as a decimal number with Monday as the first day of the week. Week 01 is the week containing Jan 4. | 01, …, 53 |
%w | Weekday as a decimal number. | 0, 1, …, 6 |
%W | Week number of the year. Week 01 starts on the first Monday of the year, so there can be week 00. Note that this is not compliant with the week date standard in ISO-8601. | 00, 01, …, 53 |
%x | ISO date representation. | 2050-08-07 |
%X | ISO time representation. | 11:45:05 |
%y | Year without century as a zero-padded decimal number. | 00, 01, …, 99 |
%-y | Year without century as a decimal number. | 0, 1, …, 99 |
%Y | Year with century as a decimal number. | 2025, 2026, 2027, … |
%z | Time offset from UTC in the form ±HH:MM, ±HHMM, or ±HH. | -0700 |
%Z | Time zone name. | Australia/Brisbane |
%% | A literal % character. | % |
Source: DuckDB website.
Example of Usage
Here’s an example that incorporates some of the above format specifiers into a query:
SELECT
strftime(DATE '2035-07-10', '%a, %d %b %y') AS short,
strftime(DATE '2035-07-10', '%A, %d %B %Y') AS long,
strftime(DATE '2035-07-10', '%A') AS day,
strftime(DATE '2035-07-10', '%B') AS month,
strftime(DATE '2035-07-10', '%Y') AS year;Result:
+----------------+-----------------------+---------+-------+------+
| short | long | day | month | year |
+----------------+-----------------------+---------+-------+------+
| Tue, 10 Jul 35 | Tuesday, 10 July 2035 | Tuesday | July | 2035 |
+----------------+-----------------------+---------+-------+------+