generate_series function in Amazon Redshift

I found a solution here for my problem of not being able to generate a time dimension table on Redshift using generate_series(). You can generate a temporary sequence by using the following SQL snippet.

with digit as (
    select 0 as d union all 
    select 1 union all select 2 union all select 3 union all
    select 4 union all select 5 union all select 6 union all
    select 7 union all select 8 union all select 9        
),
seq as (
    select a.d + (10 * b.d) + (100 * c.d) + (1000 * d.d) as num
    from digit a
        cross join
        digit b
        cross join
        digit c
        cross join
        digit d
    order by 1        
)
select (getdate()::date - seq.num)::date as "Date"
from seq;

The generate_series() function, it seems, is not supported completely on Redshift yet. If I run the SQL mentioned in the answer by DJo, it works, because the SQL runs only on the leader node. If I prepend insert into dim_time to the same SQL it doesn't work.

The version of generate_series() that supports dates and timestamps was added in Postgres 8.4.

As Redshift is based on Postgres 8.0, you need to use a different way:

select timestamp '2011-12-31 00:00:00' + (i * interval '1 day')
from  generate_series(1, (date '2012-12-31' - date '2011-12-31')) i;

If you "only" need dates, this can be abbreviated to:

select date '2011-12-31' + i
from  generate_series(1, (date '2012-12-31' - date '2011-12-31')) i;

generate_series is working with Redshift now.

SELECT CURRENT_DATE::TIMESTAMP  - (i * interval '1 day') as date_datetime 
FROM generate_series(1,31) i 
ORDER BY 1

This will generate last 30 days date