When working with SQL databases such as DuckDB, dates usually conform to a specific format. In particular, when we create dates in DuckDB, they must conform to the ISO 8601 format (YYYY-MM-DD). It’s the same with time (hh:mm:ss[.zzzzzz][+-TT[:tt]]) and timestamp values (YYYY-MM-DD hh:mm:ss[.zzzzzzzzz][+-TT[:tt]]).

But what if we have a requirement to present these dates or timestamps in a different format?

Fortunately, DuckDB provides us with tools to so. This article explains how to format date and timestamp values according to a specified format.

Quick Example

Let’s start with a quick example:

SELECT strftime(DATE '2050-12-20', '%A, %d %B %Y');

Result:

Tuesday, 20 December 2050

This is just one of any number of formats that we could use to format the date. We can also include timestamp values, and we can include the time portion in the formatted output.

Here, we used the strftime() function to format the date provided in the first argument. The second argument is our format string. The format string determines how the date will be formatted. Therefore, the format string we use will depend on the format that we’re trying to achieve. More about format strings later.

Initial Date/Time Formatting

Before getting into the details of formatting dates, let’s first look at how date/time values are initially formatted in DuckDB;

Type	Description	Example
DATE	Represents a calendar date	2025-10-05
TIMESTAMP	Represents a date and time (naive timestamp with microsecond precision)	2025-10-05 14:30:00.123456
TIMESTAMP_NS	Naive timestamp with nanosecond precision	2025-10-05 14:30:00.123456789
TIMESTAMP_MS	Naive timestamp with millisecond precision	2025-10-05 14:30:00.123
TIMESTAMP_S	Naive timestamp with second precision	2025-10-05 14:30:00
TIMESTAMPTZ	Time zone aware timestamp with microsecond precision	2025-10-05 14:30:00.123456+00:00
TIME	Represents a time of day, ignoring time zone	14:30:00
TIMETZ	Time of day, using time zone	14:30:00+05:30

We can see that each of these conform to a standardized format. But we can format them for display or export using DuckDB’s built-in functions.

While we’re at it, DuckDB also includes a few special date values that can be used on input:

Input string	Description	Output
epoch	1970-01-01 (Unix system day zero)	1970-01-01
infinity	Later than all other dates	infinity
-infinity	Earlier than all other dates	-infinity

Trying to format infinity and -infinity won’t do much, but it’s possible to format the output of epoch if required.

Using the strftime() Function for Date Formatting

As seen in the earlier example, the strftime() function allows us to convert a date or timestamp into a string representation based on a format string.

The syntax goes like this:

strftime(date, format)

Where:

• date is the date or timestamp that you need to format.
• format argument is a format string that specifies how the date or timestamp should be formatted. It uses placeholders to represent different components of the date, such as the year, month, day, hour, minute, and second.

Common Format Specifiers

The format string consists of one or more format specifiers. Here are some of the more commonly used format specifiers:

• %Y: Four-digit year (e.g., 2023).
• %y: Two-digit year (e.g., 23).
• %m: Two-digit month (e.g., 10 for October).
• %d: Two-digit day of the month (e.g., 05).
• %H: Two-digit hour in 24-hour format (e.g., 14 for 2 PM).
• %M: Two-digit minute (e.g., 30).
• %S: Two-digit second (e.g., 00).
• %A: Full weekday name (e.g., Thursday).
• %a: Abbreviated weekday name (e.g., Thu).
• %B: Full month name (e.g., October).
• %b: Abbreviated month name (e.g., Oct).

These format specifiers are basically placeholders for the relevant date part. So if we use %Y, that will result in the four-digit year being displayed, and so on.

Here’s a full list of format specifiers that you can use to build format strings.

Examples of Date Formatting with strftime()

Let’s look at some simple examples of how to use strftime() to format dates in DuckDB.

Formatting a Date

This example illustrates how we can use different format specifiers to achieve different formats:

SELECT 
    strftime(DATE '2034-08-07', '%a, %d %b %y') AS short,
    strftime(DATE '2034-08-07', '%A, %d %B %Y') AS long,
    strftime(DATE '2034-08-07', '%A') AS day,
    strftime(DATE '2034-08-07', '%B') AS month,
    strftime(DATE '2034-08-07', '%Y') AS year;

Result:

+----------------+------------------------+--------+--------+------+
|     short      |          long          |  day   | month  | year |
+----------------+------------------------+--------+--------+------+
| Mon, 07 Aug 34 | Monday, 07 August 2034 | Monday | August | 2034 |
+----------------+------------------------+--------+--------+------+

As shown here, we don’t need to include all date parts if we don’t want. For example, we can provide a single format specifier to output a single date part like we did for the day, month, and year columns.

Formatting a Timestamp

Here are some examples that use a TIMESTAMP value:

SELECT 
    strftime(TIMESTAMP '2034-08-07 12:35:15', '%d/%m/%Y %H:%M:%S') AS aus,
    strftime(TIMESTAMP '2034-08-07 12:35:15', '%m/%d/%Y %H:%M:%S') AS us,
    strftime(TIMESTAMP '2034-08-07 12:35:15', '%d/%m/%Y') AS aus_date,
    strftime(TIMESTAMP '2034-08-07 12:35:15', '%H:%M:%S') AS time,
    strftime(TIMESTAMP '2034-08-07 12:35:15', '%H:%M:%S %p') AS am_pm;

Result:

+---------------------+---------------------+------------+----------+-------------+
|         aus         |         us          |  aus_date  |   time   |    am_pm    |
+---------------------+---------------------+------------+----------+-------------+
| 07/08/2034 12:35:15 | 08/07/2034 12:35:15 | 07/08/2034 | 12:35:15 | 12:35:15 PM |
+---------------------+---------------------+------------+----------+-------------+

In Australia, dates are formatted with the day first, followed by the month (dd/mm/yyyy), whereas in the US, they’re formatted with the month first (mm/dd/yyyy). Format specifiers provide us with the flexibility to adjust our format string accordingly.

Including String Literals

We can include string literals in the formatted date. We already did this in the previous examples (by including / and : in our format strings).

Here’s an example that expands this idea further:

SELECT 
    strftime(
        DATE '2050-12-20', 
        'The day is %A on day number %d in the month of %B of the year %Y'
        ) AS result;

Result:

+-------------------------------------------------------------------------------+
|                                    result                                     |
+-------------------------------------------------------------------------------+
| The day is Tuesday on day number 20 in the month of December of the year 2050 |
+-------------------------------------------------------------------------------+

Extracting Date Components

We can see that the strftime() function is quite flexible with regards to how the various date parts are presented. We can even use the function to extract a single date part, such as the day or month or year:

SELECT 
    strftime(DATE '2034-08-07', '%-d') AS '%-d',
    strftime(DATE '2034-08-07', '%d') AS '%d',
    strftime(DATE '2034-08-07', '%-m') AS '%-m',
    strftime(DATE '2034-08-07', '%m') AS '%m',
    strftime(DATE '2034-08-07', '%A') AS '%A',
    strftime(DATE '2034-08-07', '%B') AS '%B',
    strftime(DATE '2034-08-07', '%Y') AS '%Y';

Result:

+-----+----+-----+----+--------+--------+------+
| %-d | %d | %-m | %m |   %A   |   %B   |  %Y  |
+-----+----+-----+----+--------+--------+------+
| 7   | 07 | 8   | 08 | Monday | August | 2034 |
+-----+----+-----+----+--------+--------+------+

DuckDB also provides several functions specifically for extracting date parts. Here are some of the more obvious ones:

• year(): Extracts the year.
• month(): Extracts the month.
• day(): Extracts the day.
• hour(): Extracts the hour.
• minute(): Extracts the minute.
• second(): Extracts the second.

Example:

SELECT 
    day(DATE '2034-08-07') AS 'day()',
    month(DATE '2034-08-07') AS 'month()',
    quarter(DATE '2034-08-07') AS 'quarter()',
    year(DATE '2034-08-07') AS 'year()',
    decade(DATE '2034-08-07') AS 'decade()',
    century(DATE '2034-08-07') AS 'century()';

Result:

+-------+---------+-----------+--------+----------+-----------+
| day() | month() | quarter() | year() | decade() | century() |
+-------+---------+-----------+--------+----------+-----------+
| 7     | 8       | 3         | 2034   | 203      | 21        |
+-------+---------+-----------+--------+----------+-----------+

Handling Time Zones

DuckDB also supports time zone-aware timestamps. We can use the AT TIME ZONE clause to convert a timestamp to a specific time zone before formatting:

SELECT 
    strftime(TIMESTAMP '2034-08-07 12:35:15', '%A, %d %B %Y %H:%M:%S') AS 'Local',
    strftime(TIMESTAMP '2034-08-07 12:35:15' AT TIME ZONE 'UTC', '%A, %d %B %Y %H:%M:%S') AS 'UTC';

Output:

+---------------------------------+---------------------------------+
|              Local              |               UTC               |
+---------------------------------+---------------------------------+
| Monday, 07 August 2034 12:35:15 | Monday, 07 August 2034 22:35:15 |
+---------------------------------+---------------------------------+

There’s also the timestamp() function that accepts TIMESTAMP and TIMESTAMPTZ values:

SELECT 
    timezone('UTC', TIMESTAMP '2034-08-07 12:35:15') AS 'timestamp',
    timezone('UTC', TIMESTAMPTZ '2034-08-07 12:35:15+00') AS 'timestamptz';

Output:

+------------------------+---------------------+
|       timestamp        |     timestamptz     |
+------------------------+---------------------+
| 2034-08-07 22:35:15+10 | 2034-08-07 12:35:15 |
+------------------------+---------------------+

We can pass this function to the strftime() function in order to format it the way we want:

SELECT 
    strftime(
        timezone(
            'UTC', TIMESTAMP '2034-08-07 12:35:15'), 
            '%A, %d %B %Y %H:%M:%S'
            ) AS 'timestamp',
    strftime(
        timezone(
            'UTC', TIMESTAMPTZ '2034-08-07 12:35:15+00'), 
            '%A, %d %B %Y %H:%M:%S'
            ) AS 'timestamptz';

Output:

+---------------------------------+---------------------------------+
|            timestamp            |           timestamptz           |
+---------------------------------+---------------------------------+
| Monday, 07 August 2034 22:35:15 | Monday, 07 August 2034 12:35:15 |
+---------------------------------+---------------------------------+

Using strptime() to Construct Dates

DuckDB also has a strptime() function, which enables us to convert a string to a valid timestamp value.

Example:

SELECT strptime('Monday, 7 August 2034 - 08:35:15 PM', '%A, %-d %B %Y - %I:%M:%S %p');

Result:

+--------------------------------------------------------------------------------+
| strptime('Monday, 7 August 2034 - 08:35:15 PM', '%A, %-d %B %Y - %I:%M:%S %p') |
+--------------------------------------------------------------------------------+
| 2034-08-07 20:35:15                                                            |
+--------------------------------------------------------------------------------+

So it’s basically the opposite of strftime(), in that we pass a date that’s already been formatted as a string, so that the function will convert that string to a valid timestamp value.

Summary

Formatting dates in DuckDB is relatively straightforward thanks to the strftime() function and the various format specifiers, as well as other date-related functions. Whether we need to display dates in a specific format, extract individual components, handle time zones, or construct a timestamp value from a formatted date string, DuckDB provides plenty of tools that can help.