Working with Intervals in DuckDB

Time-based operations are a fundamental aspect of data analysis and manipulation. DuckDB, the in-process analytical database management system, provides robust support for handling time-based operations through its INTERVAL data type.

This article explores the capabilities, syntax, and practical applications of intervals in DuckDB.

Understanding the `INTERVAL` Data Type

In DuckDB, an interval represents a period of time. Unlike timestamps or dates which represent specific points in time, intervals represent durations. This makes them perfect for calculations involving time differences, adding time periods to dates, or scheduling recurring events.

The INTERVAL data type in DuckDB can store components such as years, months, days, hours, minutes, seconds, etc.

Creating Intervals

DuckDB offers several ways to create interval values:

1. Using Interval Constants

The most straightforward method is using the INTERVAL keyword with a string:

SELECT 
    INTERVAL '1 day' AS interval_1,
    INTERVAL '1 year 3 months' AS interval_2,
    INTERVAL '7 hours 45 minutes 10 seconds' AS interval_3,
    INTERVAL '3 years 6 months 5 days 2 hours 30 minutes' AS interval_4;

Result:

+------------+-----------------+------------+----------------------------------+
| interval_1 |   interval_2    | interval_3 |            interval_4            |
+------------+-----------------+------------+----------------------------------+
| 1 day      | 1 year 3 months | 07:45:10   | 3 years 6 months 5 days 02:30:00 |
+------------+-----------------+------------+----------------------------------+

2. Using Duration Functions

DuckDB also provides specialized functions for common durations:

SELECT 
    TO_YEARS(3),
    TO_MONTHS(11),
    TO_DAYS(5),
    TO_HOURS(12),
    TO_MINUTES(45),
    TO_SECONDS(30);

Result:

+-------------+---------------+------------+--------------+----------------+----------------+
| to_years(3) | to_months(11) | to_days(5) | to_hours(12) | to_minutes(45) | to_seconds(30) |
+-------------+---------------+------------+--------------+----------------+----------------+
| 3 years     | 11 months     | 5 days     | 12:00:00     | 00:45:00       | 00:00:30       |
+-------------+---------------+------------+--------------+----------------+----------------+

3. Casting As Interval

We can construct an interval with a string cast:

SELECT '3.5 YEARS'::INTERVAL;

Result:

+-------------------------------+
| CAST('3.5 YEARS' AS INTERVAL) |
+-------------------------------+
| 3 years 6 months              |
+-------------------------------+

And we can use the CAST() function directly (we can see that DuckDB implicitly did this, judging by the column header in the previous example):

SELECT CAST('3.5 YEARS' AS INTERVAL);

Result:

+-------------------------------+
| CAST('3.5 YEARS' AS INTERVAL) |
+-------------------------------+
| 3 years 6 months              |
+-------------------------------+

4. Other Functions

Some functions, such as AGE() return an interval:

SELECT AGE(CURRENT_DATE, DATE '2001-12-20') AS age_interval;

Result:

+---------------------------+
|       age_interval        |
+---------------------------+
| 23 years 2 months 15 days |
+---------------------------+

About Decimal Values

We need to be careful when using decimal values, as they can be rounded to integers, depending on how we construct the interval:

SELECT 
    INTERVAL '3.5 YEARS',
    INTERVAL '3.5' YEARS;

Result:

+-------------------------------+---------------------------------------------------------+
| CAST('3.5 YEARS' AS INTERVAL) | to_years(CAST(trunc(CAST('3.5' AS DOUBLE)) AS INTEGER)) |
+-------------------------------+---------------------------------------------------------+
| 3 years 6 months              | 3 years                                                 |
+-------------------------------+---------------------------------------------------------+

We can see that INTERVAL '3.5 YEARS' recognized the decimal, whereas INTERVAL '3.5' YEARS truncated it (which set the decimal portion to zero), and cast it as an integer (which removed the decimal portion altogether).

If we must use the second method, we should provide a more granular unit (such as MONTHS in this case):

SELECT 
    INTERVAL '18 MONTHS',
    INTERVAL '18' MONTHS;

Result:

+-------------------------------+---------------------------------------------------------+
| CAST('18 MONTHS' AS INTERVAL) | to_months(CAST(trunc(CAST('18' AS DOUBLE)) AS INTEGER)) |
+-------------------------------+---------------------------------------------------------+
| 1 year 6 months               | 1 year 6 months                                         |
+-------------------------------+---------------------------------------------------------+

But we also need to be careful when doing this, as we might end up with a different unit to what we intended:

SELECT 
    INTERVAL '78 WEEKS',
    INTERVAL '78' WEEKS;

Result:

+------------------------------+--------------------------------------------------------+
| CAST('78 WEEKS' AS INTERVAL) | to_weeks(CAST(trunc(CAST('78' AS DOUBLE)) AS INTEGER)) |
+------------------------------+--------------------------------------------------------+
| 546 days                     | 546 days                                               |
+------------------------------+--------------------------------------------------------+

Combining Intervals

Intervals can be added and subtracted:

SELECT 
    INTERVAL '5 months' + INTERVAL '3 days' AS added,
    INTERVAL '5 months' - INTERVAL '3 days' AS subtracted;

Result:

+-----------------+------------------+
|      added      |    subtracted    |
+-----------------+------------------+
| 5 months 3 days | 5 months -3 days |
+-----------------+------------------+

Although, notice that the subtraction resulted in a negative day value being used, instead of 3 days being subtracted from the result.

Using Interval Multiplication & Division

We can do things like multiply intervals to create larger durations, as well as divide them into smaller durations:

SELECT 
    INTERVAL '3 days' * 5,
    INTERVAL '30 minutes' * 3,
    INTERVAL '7 years' / 2;

Result:

+----------------------------------+--------------------------------------+-----------------------------------+
| (CAST('3 days' AS INTERVAL) * 5) | (CAST('30 minutes' AS INTERVAL) * 3) | (CAST('7 years' AS INTERVAL) / 2) |
+----------------------------------+--------------------------------------+-----------------------------------+
| 15 days                          | 01:30:00                             | 3 years 6 months                  |
+----------------------------------+--------------------------------------+-----------------------------------+

Performing Date/Time Calculations with Intervals

We can also use intervals in date and time arithmetic.

SELECT 
    DATE '2025-01-15' + INTERVAL '3 months' AS add,
    DATE '2025-01-15' - INTERVAL '3 months' AS subtract;

Result:

+---------------------+---------------------+
|         add         |      subtract       |
+---------------------+---------------------+
| 2025-04-15 00:00:00 | 2024-10-15 00:00:00 |
+---------------------+---------------------+

Adding an INTERVAL to a DATE returns a TIMESTAMP, even if the INTERVAL doesn’t include a time component. It’s basically like casting the DATE as a TIMESTAMP and then adding the INTERVAL. This results in the time component being set to 00:00:00. We have the option of removing this by casting the result as a DATE:

SELECT 
    CAST(DATE '2025-01-15' + INTERVAL '3 months' AS DATE) AS add,
    CAST(DATE '2025-01-15' - INTERVAL '3 months' AS DATE) AS subtract;

Result:

+------------+------------+
|    add     |  subtract  |
+------------+------------+
| 2025-04-15 | 2024-10-15 |
+------------+------------+

Interval Comparison

We can use the equals operator (=) to compare intervals:

SELECT 
    INTERVAL '1 day' = INTERVAL '24 hours' AS c1,
    INTERVAL '1 day' = INTERVAL '15 hours' AS c2;

Result:

+------+-------+
|  c1  |  c2   |
+------+-------+
| true | false |
+------+-------+

Extracting Parts from Intervals

We can use functions like EXTRACT() to get specific components from an interval:

SELECT 
    EXTRACT('MONTH' FROM INTERVAL '1 year 3 months 5 days 26 hours 30 minutes') AS months,
    EXTRACT('DAY' FROM INTERVAL '1 year 3 months 5 days 26 hours 30 minutes') AS days,
    EXTRACT('HOUR' FROM INTERVAL '1 year 3 months 5 days 26 hours 30 minutes') AS hours,
    EXTRACT('MINUTE' FROM INTERVAL '1 year 3 months 5 days 26 hours 30 minutes') AS minutes;

Result:

+--------+------+-------+---------+
| months | days | hours | minutes |
+--------+------+-------+---------+
| 3      | 5    | 26    | 30      |
+--------+------+-------+---------+

We can do the same thing with the DATE_PART() function:

SELECT 
    DATE_PART('MONTH', INTERVAL '1 year 3 months 5 days 26 hours 30 minutes') AS months,
    DATE_PART('DAY', INTERVAL '1 year 3 months 5 days 26 hours 30 minutes') AS days,
    DATE_PART('HOUR', INTERVAL '1 year 3 months 5 days 26 hours 30 minutes') AS hours,
    DATE_PART('MINUTE', INTERVAL '1 year 3 months 5 days 26 hours 30 minutes') AS minutes;

Result:

+--------+------+-------+---------+
| months | days | hours | minutes |
+--------+------+-------+---------+
| 3      | 5    | 26    | 30      |
+--------+------+-------+---------+

Use Cases

Here are some examples of where intervals can come in handy.

1. Date Windowing

Generate date ranges for reporting:

-- Create a table with dates for the past 30 days
SELECT CAST(CURRENT_DATE - INTERVAL '1 day' * generate_series AS DATE) AS report_date
FROM generate_series(0, 6);

Result:

+-------------+
| report_date |
+-------------+
| 2025-03-04  |
| 2025-03-03  |
| 2025-03-02  |
| 2025-03-01  |
| 2025-02-28  |
| 2025-02-27  |
| 2025-02-26  |
+-------------+

2. Schedule Generation

Generate recurring dates for scheduling:

-- Generate weekly appointment slots for the next 10 weeks
SELECT base_date + INTERVAL '1 week' * series AS appointment_date
FROM (
  SELECT TIMESTAMP '2025-03-10 07:30:00' AS base_date, 
         generate_series AS series
  FROM generate_series(0, 9)
);

Result:

+---------------------+
|  appointment_date   |
+---------------------+
| 2025-03-10 07:30:00 |
| 2025-03-17 07:30:00 |
| 2025-03-24 07:30:00 |
| 2025-03-31 07:30:00 |
| 2025-04-07 07:30:00 |
| 2025-04-14 07:30:00 |
| 2025-04-21 07:30:00 |
| 2025-04-28 07:30:00 |
| 2025-05-05 07:30:00 |
| 2025-05-12 07:30:00 |
+---------------------+

3. Age Calculation

Calculate ages with precision:

SELECT AGE(CURRENT_DATE, DATE '2001-12-20') AS age_interval;

Result:

+---------------------------+
|       age_interval        |
+---------------------------+
| 23 years 2 months 15 days |
+---------------------------+

Interval Specifiers

The following specifiers can be used 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`

Understanding the INTERVAL Data Type

Creating Intervals

1. Using Interval Constants

2. Using Duration Functions

3. Casting As Interval

4. Other Functions

About Decimal Values

Combining Intervals

Using Interval Multiplication & Division

Performing Date/Time Calculations with Intervals

Interval Comparison

Extracting Parts from Intervals

Use Cases

1. Date Windowing

2. Schedule Generation

3. Age Calculation

Interval Specifiers

Understanding the `INTERVAL` Data Type