Understanding EXTRACT() in DuckDB

DuckDB offers a variety of date functions, one of which is the extract() function. This function is designed to retrieve a specific date part from a date or timestamp value, and it can also be applied to intervals.

In this article, we’ll take a closer look at the extract() function and provide some straightforward examples to illustrate how it works.

What is the `extract()` Function?

The extract() function extracts a specific part of a date/time such as the year, month, day, hour, minute, or second. It can also extract various components from an interval value.

The extract() function does the same thing that the date_part() function does, but with a different syntax.

Syntax

The syntax for the extract() function is as follows:

extract(part from date)

part: This is a string that specifies the date part to extract. Some commonly used values include 'year', 'month', 'day', 'hour', 'minute', 'second', etc. See the tables at the end of this article for a full list of values that can be used here.
date: This is the date, timestamp, or interval value from which you want to extract the specified part.

The function returns the extracted part as a numeric value.

Example

Here’s a simple example that illustrates how it works:

SELECT extract( 'year' FROM DATE '2025-07-18') AS 'year';

Result:

+------+
| year |
+------+
| 2025 |
+------+

So we pass the date part that we want to extract, followed by the FROM keyword, followed by the date itself.

Here it is with some more date parts specified:

SELECT 
    extract( 'day' FROM DATE '2025-07-18' ) AS 'day',
    extract( 'dayofweek' FROM DATE '2025-07-18') AS 'dayofweek',
    extract( 'dayofmonth' FROM DATE '2025-07-18') AS 'dayofmonth',
    extract( 'dayofyear' FROM DATE '2025-07-18') AS 'dayofyear',
    extract( 'month' FROM DATE '2025-07-18') AS 'month',
    extract( 'year' FROM DATE '2025-07-18') AS 'year',
    extract( 'century' FROM DATE '2025-07-18') AS 'century';

Result:

+-----+-----------+------------+-----------+-------+------+---------+
| day | dayofweek | dayofmonth | dayofyear | month | year | century |
+-----+-----------+------------+-----------+-------+------+---------+
| 18  | 5         | 18         | 199       | 7     | 2025 | 21      |
+-----+-----------+------------+-----------+-------+------+---------+

Compared to `date_part()` and `datepart()`

DuckDB also has a date_part() function, as well as its synonym datepart(). These functions do the same thing that extract() does, but with a slightly different syntax. Here they all are, side by side:

SELECT 
    extract( 'year' FROM DATE '2025-07-18') AS 'extract',
    date_part( 'year', DATE '2025-07-18') AS 'date_part',
    datepart( 'year', DATE '2025-07-18') AS 'datepart';

Result:

+---------+-----------+----------+
| extract | date_part | datepart |
+---------+-----------+----------+
| 2025    | 2025      | 2025     |
+---------+-----------+----------+

Both date_part()and datepart() require a comma instead of the FROM keyword. Other than that, everything else is pretty much the same.

Timestamp Values

The previous examples use a DATE value, but we can also use a TIMESTAMP value. We can use it to get the date parts or the time parts:

SELECT 
    extract( 'year' FROM TIMESTAMP '2025-07-18 18:09:39.123456' ) AS 'year',
    extract( 'hour' FROM TIMESTAMP '2025-07-18 18:09:39.123456') AS 'hour',
    extract( 'minute' FROM TIMESTAMP '2025-07-18 18:09:39.123456') AS 'minute',
    extract( 'second' FROM TIMESTAMP '2025-07-18 18:09:39.123456') AS 'second',
    extract( 'millisecond' FROM TIMESTAMP '2025-07-18 18:09:39.123456') AS 'millisecond',
    extract( 'microsecond' FROM TIMESTAMP '2025-07-18 18:09:39.123456') AS 'microsecond';

Result:

+------+------+--------+--------+-------------+-------------+
| year | hour | minute | second | millisecond | microsecond |
+------+------+--------+--------+-------------+-------------+
| 2025 | 18   | 9      | 39     | 39123       | 39123456    |
+------+------+--------+--------+-------------+-------------+

Interval Values

We can also use the extract() function on intervals:

SELECT 
    extract( 'year' FROM INTERVAL '10 years 5 months 4 days 3 hours 45 minutes') AS 'years',
    extract( 'months' FROM INTERVAL '10 years 5 months 4 days 3 hours 45 minutes') AS 'months',
    extract( 'days' FROM INTERVAL '10 years 5 months 4 days 3 hours 45 minutes') AS 'days',
    extract( 'hours' FROM INTERVAL '10 years 5 months 4 days 3 hours 45 minutes') AS 'hours',
    extract( 'minutes' FROM INTERVAL '10 years 5 months 4 days 3 hours 45 minutes') AS 'minutes';

Result:

+-------+--------+------+-------+---------+
| years | months | days | hours | minutes |
+-------+--------+------+-------+---------+
| 10    | 5      | 4    | 3     | 45      |
+-------+--------+------+-------+---------+

There are quite a few date specifiers that can be used with the extract() function, but not all of them can be used on interval values. See the table later in this article for a list of specifiers that can be used with intervals.

Database Examples

Suppose we have the following table and data:

-- Create the 'sales' table
CREATE TABLE sales (
    sale_id INTEGER PRIMARY KEY,
    sale_date TIMESTAMP,
    amount DECIMAL(10, 2)
);

-- Insert sample data into the 'sales' table
INSERT INTO sales (sale_id, sale_date, amount) VALUES
(1, '2024-01-15 09:30:45', 100.50),
(2, '2024-01-20 14:15:10', 200.75),
(3, '2024-01-25 11:45:30', 150.00),
(4, '2024-02-10 16:20:55', 300.25),
(5, '2024-02-12 08:05:20', 250.60),
(6, '2024-03-05 12:35:40', 175.90),
(7, '2024-03-18 18:50:15', 400.00),
(8, '2024-03-22 10:10:10', 225.30),
(9, '2024-04-01 13:25:50', 275.45),
(10, '2024-04-05 20:40:05', 500.75),
(11, '2024-04-10 07:55:25', 125.80),
(12, '2024-05-03 22:00:00', 350.90);

-- Query to verify the data
SELECT * FROM sales;

Output:

+---------+---------------------+--------+
| sale_id |      sale_date      | amount |
+---------+---------------------+--------+
| 1       | 2024-01-15 09:30:45 | 100.50 |
| 2       | 2024-01-20 14:15:10 | 200.75 |
| 3       | 2024-01-25 11:45:30 | 150.00 |
| 4       | 2024-02-10 16:20:55 | 300.25 |
| 5       | 2024-02-12 08:05:20 | 250.60 |
| 6       | 2024-03-05 12:35:40 | 175.90 |
| 7       | 2024-03-18 18:50:15 | 400.00 |
| 8       | 2024-03-22 10:10:10 | 225.30 |
| 9       | 2024-04-01 13:25:50 | 275.45 |
| 10      | 2024-04-05 20:40:05 | 500.75 |
| 11      | 2024-04-10 07:55:25 | 125.80 |
| 12      | 2024-05-03 22:00:00 | 350.90 |
+---------+---------------------+--------+

Example 1: Extract the Month from a Date

Let’s start by extracting the month from the sale_date column:

SELECT 
    sale_id,
    extract('month' FROM sale_date) AS sale_month
FROM sales;

Output:

+---------+------------+
| sale_id | sale_month |
+---------+------------+
| 1       | 1          |
| 2       | 1          |
| 3       | 1          |
| 4       | 2          |
| 5       | 2          |
| 6       | 3          |
| 7       | 3          |
| 8       | 3          |
| 9       | 4          |
| 10      | 4          |
| 11      | 4          |
| 12      | 5          |
+---------+------------+

Example 2: Filter Data by Month

If we want to filter sales that occurred in a specific month, such as April (month 4), we can use the extract() function in the WHERE clause:

SELECT *
FROM sales
WHERE extract('month' FROM sale_date) = 4;

Output:

+---------+---------------------+--------+
| sale_id |      sale_date      | amount |
+---------+---------------------+--------+
| 9       | 2024-04-01 13:25:50 | 275.45 |
| 10      | 2024-04-05 20:40:05 | 500.75 |
| 11      | 2024-04-10 07:55:25 | 125.80 |
+---------+---------------------+--------+

Example 3: Group the Sales by Month

In this example we use the GROUP BY clause to group the sales by month:

SELECT
    extract('month' FROM sale_date) AS sale_month,
    SUM(amount) AS total_sales
FROM sales
GROUP BY sale_month
ORDER BY sale_month;

Output:

+------------+-------------+
| sale_month | total_sales |
+------------+-------------+
| 1          | 451.25      |
| 2          | 550.85      |
| 3          | 801.20      |
| 4          | 902.00      |
| 5          | 350.90      |
+------------+-------------+

In this case we also used the SUM() function to tally up the sales for the respective month.

More Functionality with `strftime()`

We’ve already seen that the date_part() and datepart() functions can do exactly what extract() does. But DuckDB also provides other functions that can do the same thing, and more in some cases.

In particular, the strftime() function can be used to format a whole date into a specified format. It returns its output as a string, and so we can have string characters in the result. This can be handy if we want to get the day or month name from the date.

Let’s use strftime() in order to get the month name instead of the month number:

SELECT
    strftime(sale_date, '%B') AS sale_month,
    SUM(amount) AS total_sales
FROM sales
GROUP BY sale_month
ORDER BY sale_month;

Result:

+------------+-------------+
| sale_month | total_sales |
+------------+-------------+
| April      | 902.00      |
| February   | 550.85      |
| January    | 451.25      |
| March      | 801.20      |
| May        | 350.90      |
+------------+-------------+

Bear in mind that doing this changes the ordering of the results. Ordering by month name will not be the same as ordering by month number. So that’s something to be mindful of.

See Formatting Dates with strftime() for more information and examples.

Supported Date Parts

The two tables below contain the various date parts that can be specified when using the extract() function. Some of these can be used with both dates and intervals, while others can only be used with dates.

Date Part Specifiers and in Intervals

The following specifiers can be used in dates/timestamps and also with intervals:

Specifier	Description	Synonyms	Example
`century`	Gregorian century	`cent`, `centuries`, `c`	`21`
`day`	Gregorian day	`days`, `d`, `dayofmonth`	`3`
`decade`	Gregorian decade	`dec`, `decades`, `decs`	`202`
`hour`	Hours	`hr`, `hours`, `hrs`, `h`	`11`
`microseconds`	Sub-minute microseconds	`microsecond`, `us`, `usec`, `usecs`, `usecond`, `useconds`	`44123456`
`millennium`	Gregorian millennium	`mil`, `millenniums`, `millenia`, `mils`, `millenium`	`3`
`milliseconds`	Sub-minute milliseconds	`millisecond`, `ms`, `msec`, `msecs`, `msecond`, `mseconds`	`44123`
`minute`	Minutes	`min`, `minutes`, `mins`, `m`	`59`
`month`	Gregorian month	`mon`, `months`, `mons`	`8`
`quarter`	Quarter of the year (1-4)	`quarters`	`3`
`second`	Seconds	`sec`, `seconds`, `secs`, `s`	`44`
`year`	Gregorian year	`yr`, `y`, `years`, `yrs`	`2021`

Date Part Specifiers Only

The following specifiers can only be used with date/time values (i.e. they can’t be used with intervals):

Specifier	Description	Synonyms	Example
`dayofweek`	Day of the week (Sunday = 0, Saturday = 6)	`weekday`, `dow`	`2`
`dayofyear`	Day of the year (1-365/366)	`doy`	`215`
`epoch`	Seconds since 1970-01-01		`1627991984`
`era`	Gregorian era (CE/AD, BCE/BC)		`1`
`isodow`	ISO day of the week (Monday = 1, Sunday = 7)		`2`
`isoyear`	ISO Year number (Starts on Monday of week containing Jan 4th)		`2021`
`timezone_hour`	Time zone offset hour portion		`0`
`timezone_minute`	Time zone offset minute portion		`0`
`timezone`	Time zone offset in seconds		`0`
`week`	Week number	`weeks`, `w`	`31`
`yearweek`	ISO year and week number in `YYYYWW` format		`202131`

The above tables are sourced from the DuckDB documentation.

If you can’t find a specifier for the job you need to perform, see the list of format specifiers that can be used with the strftime() function. These include format specifiers that do things like, return the day or month name, return a zero padded day or month number, etc.

What is the extract() Function?

Syntax

Example

Compared to date_part() and datepart()

Timestamp Values

Interval Values

Database Examples

Example 1: Extract the Month from a Date

Example 2: Filter Data by Month

Example 3: Group the Sales by Month

More Functionality with strftime()

Supported Date Parts

Date Part Specifiers and in Intervals

Date Part Specifiers Only

What is the `extract()` Function?

Compared to `date_part()` and `datepart()`

More Functionality with `strftime()`