'How to Generate Date Series in Redshift?

I would like to generate a sequence of dates in Redshift. I can easily do it in PostgreSQL using the generate_series() function. This function works in Redshift, but only on the master node, so it fails when you have to use it on temporary tables to apply some joins.

Do you know of any other way to do this without using the generate_series() function?

SELECT  generate_series(66,116) as sprint,
        to_char(DATE '2022-03-01' + (interval '2 week' * generate_series(0,50)), 'YYYY-MM-DD') AS start_date,
        to_char(DATE '2022-03-15' + (interval '2 week' * generate_series(0,50)), 'YYYY-MM-DD') AS end_date

enter image description here

sqlamazon-redshift


Solution 1:[1]

You can do this easily in Redshift with a recursive CTE. I answered a similar question a little while back here ...

How to get the minimal value for all rows before x days in the past in Redshift

Let me know if this doesn't address you issue or you need help applying this answer to your question.

Solution 2:[2]

I was able to solve it.

select n+65 as sprint,
       DATEADD('week', (n + (n-1)), DATE('2022-02-22')) AS start_date, 
       DATEADD('week', (n + (n-1)), DATE('2022-03-08')) AS end_date
    FROM (SELECT ROW_NUMBER()  OVER () AS n FROM stl_scan LIMIT 100) n
    ORDER BY start_date asc

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Bill Weiner
Solution 2 karel

Related Questions