Generating Date Ranges with DuckDB Queries

by

Working with date ranges is a common requirement in data analysis, reporting, and time-series operations. DuckDB provides us with several approaches for generating date ranges. This article explores various techniques for creating date ranges in DuckDB.

Using generate_series() for Date Sequences

The most straightforward way of generating a date range in DuckDB is to use the generate_series() function. This function creates a sequence of values between a start and end point with a specified step interval.

For example, we can use the function to do this:

.mode csv
SELECT generate_series(
    DATE '2025-01-01',
    DATE '2025-01-31',
    INTERVAL '1 day'
);

Output:

"['2025-01-01 00:00:00', '2025-01-02 00:00:00', '2025-01-03 00:00:00', '2025-01-04 00:00:00', '2025-01-05 00:00:00', '2025-01-06 00:00:00', '2025-01-07 00:00:00', '2025-01-08 00:00:00', '2025-01-09 00:00:00', '2025-01-10 00:00:00', '2025-01-11 00:00:00', '2025-01-12 00:00:00', '2025-01-13 00:00:00', '2025-01-14 00:00:00', '2025-01-15 00:00:00', '2025-01-16 00:00:00', '2025-01-17 00:00:00', '2025-01-18 00:00:00', '2025-01-19 00:00:00', '2025-01-20 00:00:00', '2025-01-21 00:00:00', '2025-01-22 00:00:00', '2025-01-23 00:00:00', '2025-01-24 00:00:00', '2025-01-25 00:00:00', '2025-01-26 00:00:00', '2025-01-27 00:00:00', '2025-01-28 00:00:00', '2025-01-29 00:00:00', '2025-01-30 00:00:00', '2025-01-31 00:00:00']"

This query generates all dates from January 1, 2025, to January 31, 2025, incrementing by one day. In this case I output the result in csv mode, but you can use other modes.

Customizing the Step Interval

You can modify the step interval to generate different date patterns.

For example, you can generate weekly dates:

SELECT generate_series(
    DATE '2025-01-01',
    DATE '2025-01-31',
    INTERVAL '1 week'
);

Output:

"['2025-01-01 00:00:00', '2025-01-08 00:00:00', '2025-01-15 00:00:00', '2025-01-22 00:00:00', '2025-01-29 00:00:00']"

Monthly dates (first day of each month):

SELECT generate_series(
    DATE '2025-01-01',
    DATE '2025-12-01',
    INTERVAL '1 month'
);

Output:

"['2025-01-01 00:00:00', '2025-02-01 00:00:00', '2025-03-01 00:00:00', '2025-04-01 00:00:00', '2025-05-01 00:00:00', '2025-06-01 00:00:00', '2025-07-01 00:00:00', '2025-08-01 00:00:00', '2025-09-01 00:00:00', '2025-10-01 00:00:00', '2025-11-01 00:00:00', '2025-12-01 00:00:00']"

Quarterly dates:

SELECT generate_series(
    DATE '2025-01-01',
    DATE '2025-10-01',
    INTERVAL '3 months'
);

Output:

"['2025-01-01 00:00:00', '2025-04-01 00:00:00', '2025-07-01 00:00:00', '2025-10-01 00:00:00']"

Working with TIMESTAMP Components

Here’s an example that uses a timestamp range:

SELECT generate_series(
    TIMESTAMP '2025-01-01 00:00:00',
    TIMESTAMP '2025-01-01 23:59:59',
    INTERVAL '1 hour'
);

Output:

"['2025-01-01 00:00:00', '2025-01-01 01:00:00', '2025-01-01 02:00:00', '2025-01-01 03:00:00', '2025-01-01 04:00:00', '2025-01-01 05:00:00', '2025-01-01 06:00:00', '2025-01-01 07:00:00', '2025-01-01 08:00:00', '2025-01-01 09:00:00', '2025-01-01 10:00:00', '2025-01-01 11:00:00', '2025-01-01 12:00:00', '2025-01-01 13:00:00', '2025-01-01 14:00:00', '2025-01-01 15:00:00', '2025-01-01 16:00:00', '2025-01-01 17:00:00', '2025-01-01 18:00:00', '2025-01-01 19:00:00', '2025-01-01 20:00:00', '2025-01-01 21:00:00', '2025-01-01 22:00:00', '2025-01-01 23:00:00']"

Generating Business Days Only

To generate only business days (Monday through Friday), you can filter the generated series:

.mode table
SELECT date_value
FROM unnest(
  generate_series(
    DATE '2025-01-01',
    DATE '2025-01-31',
    INTERVAL '1 day'
  )
) AS t(date_value)
WHERE date_part('dayofweek', date_value) BETWEEN 1 AND 5;

Output:

+---------------------+
|     date_value      |
+---------------------+
| 2025-01-01 00:00:00 |
| 2025-01-02 00:00:00 |
| 2025-01-03 00:00:00 |
| 2025-01-06 00:00:00 |
| 2025-01-07 00:00:00 |
| 2025-01-08 00:00:00 |
| 2025-01-09 00:00:00 |
| 2025-01-10 00:00:00 |
| 2025-01-13 00:00:00 |
| 2025-01-14 00:00:00 |
| 2025-01-15 00:00:00 |
| 2025-01-16 00:00:00 |
| 2025-01-17 00:00:00 |
| 2025-01-20 00:00:00 |
| 2025-01-21 00:00:00 |
| 2025-01-22 00:00:00 |
| 2025-01-23 00:00:00 |
| 2025-01-24 00:00:00 |
| 2025-01-27 00:00:00 |
| 2025-01-28 00:00:00 |
| 2025-01-29 00:00:00 |
| 2025-01-30 00:00:00 |
| 2025-01-31 00:00:00 |
+---------------------+

For this example I switched to table mode in order to output the results in a column. I also used the unnest() function to unnest the series returned by generate_series().

The date_part() function returns the part of the date that we specify. So we’re able to use this function to filter the query to just those dates that have a dayofweek value of between 1 and 5.

Using the range() Function

DuckDB also provides us with the range() function, which does a similar thing to the generate_series() function:

.mode csv
SELECT range(
    DATE '2025-01-01',
    DATE '2025-01-31',
    INTERVAL '1 day'
);

Output:

"['2025-01-01 00:00:00', '2025-01-02 00:00:00', '2025-01-03 00:00:00', '2025-01-04 00:00:00', '2025-01-05 00:00:00', '2025-01-06 00:00:00', '2025-01-07 00:00:00', '2025-01-08 00:00:00', '2025-01-09 00:00:00', '2025-01-10 00:00:00', '2025-01-11 00:00:00', '2025-01-12 00:00:00', '2025-01-13 00:00:00', '2025-01-14 00:00:00', '2025-01-15 00:00:00', '2025-01-16 00:00:00', '2025-01-17 00:00:00', '2025-01-18 00:00:00', '2025-01-19 00:00:00', '2025-01-20 00:00:00', '2025-01-21 00:00:00', '2025-01-22 00:00:00', '2025-01-23 00:00:00', '2025-01-24 00:00:00', '2025-01-25 00:00:00', '2025-01-26 00:00:00', '2025-01-27 00:00:00', '2025-01-28 00:00:00', '2025-01-29 00:00:00', '2025-01-30 00:00:00']"