DuckDB provides us with a good selection of functions for working with date/time values. Among them is date_part(), which we can use to extract specific components from dates, timestamps, and intervals.
In this article, weāll look how the date_part() function works, along with some basic examples.
What is the date_part() Function?
The date_part() function in DuckDB is used to extract a specific part of a date/time such as the year, month, day, hour, minute, or second. It can also extract such components from an interval. The function is especially helpful when you need to perform time-based analysis or filter data based on specific time components.
Syntax of date_part()
The syntax for the date_part() function is as follows:
date_part(part, source)part: This is a string that specifies the part of the date or timestamp you want to extract. Common values include'year','month','day','hour','minute','second', and more. See the tables at the end of this article for a full list.source: This is the date or timestamp value from which you want to extract the specified part. It can be a column name, a literal date, a timestamp, or an interval.
The function returns the extracted part as a numeric value.
There’s also a datepart() function, which is a synonym of date_part(). Its syntax goes like this:
datepart(part, source)The part and source arguments are the same as above.
Example
Here’s a simple example that illustrates how it works:
SELECT date_part( 'year', DATE '2025-04-06') AS 'year';Result:
+------+
| year |
+------+
| 2025 |
+------+
So, quite straightforward. We pass the date part that we want to extract, followed by the date itself, and date_part() returns that date part from the date.
And here it is with some more date parts specified:
SELECT
date_part( 'day', DATE '2025-04-06' ) AS 'day',
date_part( 'dayofweek', DATE '2025-04-06') AS 'dayofweek',
date_part( 'dayofmonth', DATE '2025-04-06') AS 'dayofmonth',
date_part( 'dayofyear', DATE '2025-04-06') AS 'dayofyear',
date_part( 'month', DATE '2025-04-06') AS 'month',
date_part( 'year', DATE '2025-04-06') AS 'year',
date_part( 'century', DATE '2025-04-06') AS 'century';Result:
+-----+-----------+------------+-----------+-------+------+---------+
| day | dayofweek | dayofmonth | dayofyear | month | year | century |
+-----+-----------+------------+-----------+-------+------+---------+
| 6 | 0 | 6 | 96 | 4 | 2025 | 21 |
+-----+-----------+------------+-----------+-------+------+---------+
Using datepart()
As mentioned, datepart() is a synonym of date_part(), and so we can do the following:
SELECT datepart( 'year', DATE '2025-04-06') AS 'year';Result:
+------+
| year |
+------+
| 2025 |
+------+
All we did was replace date_part with datepart. Everything else remained the same.
Timestamp Values
The previous example uses 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
date_part( 'year', TIMESTAMP '2025-04-06 12:35:46.123456' ) AS 'year',
date_part( 'hour', TIMESTAMP '2025-04-06 12:35:46.123456') AS 'hour',
date_part( 'minute', TIMESTAMP '2025-04-06 12:35:46.123456') AS 'minute',
date_part( 'second', TIMESTAMP '2025-04-06 12:35:46.123456') AS 'second',
date_part( 'millisecond', TIMESTAMP '2025-04-06 12:35:46.123456') AS 'millisecond',
date_part( 'microsecond', TIMESTAMP '2025-04-06 12:35:46.123456') AS 'microsecond';Result:
+------+------+--------+--------+-------------+-------------+
| year | hour | minute | second | millisecond | microsecond |
+------+------+--------+--------+-------------+-------------+
| 2025 | 12 | 35 | 46 | 46123 | 46123456 |
+------+------+--------+--------+-------------+-------------+
Interval Values
We can also use the date_part() function on intervals:
SELECT
date_part( 'year', INTERVAL '3 years 2 months 5 days 9 hours 37 minutes') AS 'years',
date_part( 'months', INTERVAL '3 years 2 months 5 days 9 hours 37 minutes') AS 'months',
date_part( 'days', INTERVAL '3 years 2 months 5 days 9 hours 37 minutes') AS 'days',
date_part( 'hours', INTERVAL '3 years 2 months 5 days 9 hours 37 minutes') AS 'hours',
date_part( 'minutes', INTERVAL '3 years 2 months 5 days 9 hours 37 minutes') AS 'minutes';Result:
+-------+--------+------+-------+---------+
| years | months | days | hours | minutes |
+-------+--------+------+-------+---------+
| 3 | 2 | 5 | 9 | 37 |
+-------+--------+------+-------+---------+
Not all date specifiers 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
Here are some examples that use a database to see how the date_part() function can be used in real-world scenarios.
First, let’s create a table and populate it with 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: Extracting the Month from a Date
Let’s start by extracting the month from the sale_date column:
SELECT
sale_id,
date_part('month', 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: Filtering Data by Month
If we want to filter sales that occurred in a specific month, such as March (month 3), we can use the date_part() function in the WHERE clause:
SELECT *
FROM sales
WHERE date_part('month', sale_date) = 3;Output:
+---------+---------------------+--------+
| sale_id | sale_date | amount |
+---------+---------------------+--------+
| 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 |
+---------+---------------------+--------+
Example 3: Grouping the Sales by Month
Here’s one where we use the GROUP BY clause to group the sales by month:
SELECT
date_part('month', 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 |
+------------+-------------+
Here, we used the SUM() function to return a total of the sales for the respective month.
An Alternative to date_part()
While the date_part() function can be very handy for extracting date parts from a date, it’s not the only function that can do that. And sometimes we might need to go a bit further and do something like, extract the actual month name from the date. In this case, we might want to use the strftime() function instead of date_part().
Therefore, we can rewrite the previous example to use strftime() in order to get the month name, instead of just 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 |
+------------+-------------+
You’ll notice that this changes the order of the rows, due to it being grouped by month name instead of month number. That said, the query could be modified to include the month number.
See Formating Dates with strftime() for more info and examples.
Supported Date Parts
The date_part() function supports a wide range of date parts. 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.