Exploring the RANGE() Function in DuckDB

The range() function is a handy utility in DuckDB that enables us to generate sequences of numbers or timestamps. We specify the starting point, the end point, and the step to use for each value in the range.

In this article, we’ll take a look at DuckDB’s range() function, along with some basic examples.

Syntax

The range() function can be used on numbers or timestamp values.

When used on numbers, it can be used in the following ways:

range(stop)
range(start, stop)
range(start, stop, step)

When used on timestamp values, the syntax goes like this:

range(start, stop, interval)

So we have a bit more flexibility when creating a range of numbers when compared to timestamps. Note that timestamps don’t necessarily have to be TIMESTAMP type. They can be DATE, TIMESTAMP, or TIMESTAMPTZ (timestamp with time zone) values. That said, passing DATE values will produce a range of TIMESTAMP values. Passing TIMESTAMPTZ values will produce TIMESTAMPTZ values.

Here’s a more detailed syntax:

range(BIGINT) -> BIGINT[]
range(BIGINT, BIGINT) -> BIGINT[]
range(BIGINT, BIGINT, BIGINT) -> BIGINT[]
range(TIMESTAMP, TIMESTAMP, INTERVAL) -> TIMESTAMP[]
range(TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE, INTERVAL) -> TIMESTAMP WITH TIME ZONE[]

Basic Numeric Series

When we generate a range of numbers, we have the option of specifying the start and end point, or just the end point. We also have the option of specifying a step amount.

Let’s starting by specifying the end point:

SELECT range(5);

Result:

+-----------------+
|   "range"(5)    |
+-----------------+
| [0, 1, 2, 3, 4] |
+-----------------+

The endpoint or “stop” value is exclusive. By that I mean that it doesn’t include that value in the result. The example illustrates this point clearly. We provided a stop value of 5, and the range of numbers only went up to 4. This is in contrast to the generate_series() function, which does a similar thing to range() except that the stop value is inclusive. If we’d used generate_series() for this example, the series would’ve gone up to 5.

Specifying a Start Point

Here’s an example that uses a start point:

SELECT range(1, 5);

Result:

+---------------+
| "range"(1, 5) |
+---------------+
| [1, 2, 3, 4]  |
+---------------+

The start value is inclusive, unlike the stop value. We specified 1 as the start value, and 1 is exactly where the range started.

Specifying a Step

We have the option of providing a third argument for the step value. Here’s an example where we generate even numbers from 0 to 10:

SELECT range(0, 10, 2);

Output:

+-------------------+
| "range"(0, 10, 2) |
+-------------------+
| [0, 2, 4, 6, 8]   |
+-------------------+

Again, the stop value is exclusive, and so it only went up to 8.

Negative Steps

Negative steps work for descending sequences:

SELECT range(10, 0, -2);

Output:

+--------------------+
| "range"(10, 0, -2) |
+--------------------+
| [10, 8, 6, 4, 2]   |
+--------------------+

Unnest the Results

We can use the unnest() function to return the values as separate rows in a table:

SELECT unnest(range(1,5));

Result:

+-----------------------+
| unnest("range"(1, 5)) |
+-----------------------+
| 1                     |
| 2                     |
| 3                     |
| 4                     |
+-----------------------+

Another way to do it is like this:

SELECT * FROM range(1,5);

Result:

+-------+
| range |
+-------+
| 1     |
| 2     |
| 3     |
| 4     |
+-------+

Generating a Date Series

We can also use the range() function to create timestamp sequences:

SELECT range(
    DATE '2031-10-01',
    DATE '2031-10-07',
    INTERVAL '1 day'
) AS result;

Result:

+--------------------------------------------------------------------------------------------------------------------------------+
|                                                             result                                                             |
+--------------------------------------------------------------------------------------------------------------------------------+
| [2031-10-01 00:00:00, 2031-10-02 00:00:00, 2031-10-03 00:00:00, 2031-10-04 00:00:00, 2031-10-05 00:00:00, 2031-10-06 00:00:00] |
+--------------------------------------------------------------------------------------------------------------------------------+

We can also unnest the result in order to have the timestamps returned in a table:

SELECT * FROM range(
    DATE '2031-10-01',
    DATE '2031-10-07',
    INTERVAL '1 day'
);

Result:

+---------------------+
|        range        |
+---------------------+
| 2031-10-01 00:00:00 |
| 2031-10-02 00:00:00 |
| 2031-10-03 00:00:00 |
| 2031-10-04 00:00:00 |
| 2031-10-05 00:00:00 |
| 2031-10-06 00:00:00 |
+---------------------+

This makes it a bit easier to read than with an array of timestamps.

We can add a third argument in order to specify an interval, such as weeks:

SELECT * FROM range(
    DATE '2031-01-06',
    DATE '2031-03-31',
    INTERVAL '1 week'
);

Result:

+---------------------+
|        range        |
+---------------------+
| 2031-01-06 00:00:00 |
| 2031-01-13 00:00:00 |
| 2031-01-20 00:00:00 |
| 2031-01-27 00:00:00 |
| 2031-02-03 00:00:00 |
| 2031-02-10 00:00:00 |
| 2031-02-17 00:00:00 |
| 2031-02-24 00:00:00 |
| 2031-03-03 00:00:00 |
| 2031-03-10 00:00:00 |
| 2031-03-17 00:00:00 |
| 2031-03-24 00:00:00 |
+---------------------+

Timestamp Series

The range() function returns its result as a TIMESTAMP, even when we pass DATE values (or as a TIMESTAMPTZ when passed TIMESTAMPTZ values). Let’s pass actual TIMESTAMP values to generate a time series across a single day:

SELECT * FROM range(
    TIMESTAMP '2031-12-20 09:00:00',
    TIMESTAMP '2031-12-20 18:00:00',
    INTERVAL '1 hour'
);

Output:

+---------------------+
|        range        |
+---------------------+
| 2031-12-20 09:00:00 |
| 2031-12-20 10:00:00 |
| 2031-12-20 11:00:00 |
| 2031-12-20 12:00:00 |
| 2031-12-20 13:00:00 |
| 2031-12-20 14:00:00 |
| 2031-12-20 15:00:00 |
| 2031-12-20 16:00:00 |
| 2031-12-20 17:00:00 |
+---------------------+

Timestamp With Time Zone

As mentioned, the range() function also supports TIMESTAMP WITH TIME ZONE values (also known as TIMESTAMPTZ):

SELECT * FROM range(
    TIMESTAMPTZ '2035-09-12 09:00:00+02:30',
    TIMESTAMPTZ '2035-09-12 18:00:00+02:30',
    INTERVAL '1 hour'
);

Here’s what that returns on my machine:

+------------------------+
|         range          |
+------------------------+
| 2035-09-12 16:30:00+10 |
| 2035-09-12 17:30:00+10 |
| 2035-09-12 18:30:00+10 |
| 2035-09-12 19:30:00+10 |
| 2035-09-12 20:30:00+10 |
| 2035-09-12 21:30:00+10 |
| 2035-09-12 22:30:00+10 |
| 2035-09-12 23:30:00+10 |
| 2035-09-13 00:30:00+10 |
+------------------------+