How can you generate a date list from a range in Amazon Redshift?

Question

Getting date list in a range in PostgreSQL shows how to get a date range in PostgreSQL. However, Redshift does not support generate_series():

ans=> select (generate_series('2012-06-29', '2012-07-03', '1 day'::interval))::date;
ERROR:  function generate_series("unknown", "unknown", interval) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.

Is there way to replicate what generate_series() does in Redshift?

Answer 1

a hack, but works:

use a table with many many rows, and a window function to generate the series

this works as long as you are generating a series that is smaller than the number of rows in the table you're using to generate the series

WITH x(dt) AS (SELECT '2016-01-01'::date)
SELECT 
    dateadd(
        day, 
        COUNT(*) over(rows between unbounded preceding and current row) - 1, 
    dt)
FROM users, x 
LIMIT 100

the initial date 2016-01-01 controls the start date, and the limit controls the number of days in the generated series.

Update: * Will only run on the leader node

Redshift has partial support for the generate_series function but unfortunately does not mention it in their documentation.

This will work and is the shortest & most legible way of generating a series of dates as of this date (2018-01-29):

SELECT ('2016-01-01'::date + x)::date 
FROM generate_series(1, 100, 1) x