How can I expand six days beyond each date in a list of arbitrary dates?

Question

I have a list of arbitrary dates. I would like to expand the list to include the six days following each date. I am using Starburst/Trino SQL.

For example, given the following table of dates (let's call the table startdates).

arbitrary_date
1/5/2023
2/23/2023
4/6/2023
5/11/2023
6/15/2023

I would like the following output table.

expanded_dates
1/5/2023
1/6/2023
1/7/2023
1/8/2023
1/9/2023
1/10/2023
1/11/2023
2/23/2023
2/24/2023
2/25/2023
2/26/2023
2/27/2023
2/28/2023
3/1/2023
4/6/2023
4/7/2023
4/8/2023
4/9/2023
4/10/2023
4/11/2023
4/12/2023
5/29/2023
5/30/2023
5/31/2023
6/1/2023
6/2/2023
6/3/2023
6/4/2023
7/15/2023
7/16/2023
7/17/2023
7/18/2023
7/19/2023
7/20/2023
7/21/2023

My terrible solution is the following.

CREATE TABLE alldates AS 
SELECT  arbitrary_date AS date0,
    arbitrary_date + INTERVAL '1' DAY AS date1,
    arbitrary_date + INTERVAL '2' DAY AS date2,
    arbitrary_date + INTERVAL '3' DAY AS date3,
    arbitrary_date + INTERVAL '4' DAY AS date4,
    arbitrary_date + INTERVAL '5' DAY AS date5,
    arbitrary_date + INTERVAL '6' DAY AS date6
FROM startdates
;

Then, using the above table...

SELECT new_date FROM 
(
SELECT date0 AS new_date FROM alldates
UNION
SELECT date1 AS new_date FROM alldates
UNION
SELECT date2 AS new_date FROM alldates
UNION
SELECT date3 AS new_date FROM alldates
UNION
SELECT date4 AS new_date FROM alldates
UNION
SELECT date5 AS new_date FROM alldates
UNION
SELECT date6 AS new_date FROM alldates
)
;

This obviously "works," but what if I wanted 10 days? or 20 days? or a month?

With two "bookend" dates I can get a table by using the following, but I can't figure out how to apply this when I have a list of "bookend" dates or start dates in a table.

SELECT * FROM UNNEST(SEQUENCE(date'2023-02-07', date'2023-02-13', INTERVAL '1' DAY)) AS t1(new_date);

new_date
2023-02-07
2023-02-08
2023-02-09
2023-02-10
2023-02-11
2023-02-12
2023-02-13

Answer 1

You were really close here, the sequence + unnest (notice the succinct syntax for unnest) is way to go, use date calculation to create the second boundary:

-- sample data
WITH dataset(arbitrary_date) AS (
   values (date '2023-01-05'),
        (date '2023-02-23')
)
-- query
select d
from dataset
    ,unnest (sequence(arbitrary_date, arbitrary_date + interval '6' day, interval '1' day)) as t(d);

Output:

d
2023-01-05
2023-01-06
2023-01-07
2023-01-08
2023-01-09
2023-01-10
2023-01-11
2023-02-23
2023-02-24
2023-02-25
2023-02-26
2023-02-27
2023-02-28
2023-03-01