How can I capture date intervals dynamically using recursion or another method in Redshift?

Question

I have this in a CTE in Redshift:

select
cast('2021-03-31' as date) as contract_start_date,
cast('2025-03-30' as date) as contract_end_date,
datediff(month, '2021-03-31', '2025-03-30') as number_of_months,
'abc' as client_id;

I want to output a long table that captures the date every six months starting from the contract_start_date to the contract_end_date. The output of the code should look like this:

client_id   date_interval
abc         9-30-21
abc         3-30-22
abc         9-30-22
abc         3-30-23
abc         9-30-23
abc         3-30-24
abc         9-30-24
abc         3-30-25

I know that I can hard code this using dateadd functions in separate CTEs, but contract durations can vary widely. I prefer to have dynamic code that, starting with the enddate, subtracts 6 months iteratively until 0 months remain, capturing each date in a separate row with each iteration. Can this be done recursively? If this is too complicated in Redshift, I can probably do it in Python, but that is a last resort and performance will suffer.

Answer 1

Redshift recently added support for recursive queries: yay!

I think the logic you want is:

with recursive cte (client_id, date_interval, end_date) as (
    select client_id, contract_start_date from mytable
    union all
    select client_id, dateadd(month, 3, date_interval), end_date
    from cte
    where date_interval < end_date
)
select client_id, date_interval from cte

You can hardcode the values in the anchor of the query rather than select ... from mytable if you prefer (as shown in your example).

If the contract end dates do not exactly align to the start dates (modulo three months), you might want to adapt the where clause of the recursive member.