generate_series() method fails in Redshift

The generate_series() function is not fully supported by Redshift. See the Unsupported PostgreSQL functions section of the developer guide:

In the specific examples, the second query is executed entirely on the leader node as it does not need to scan any actual table data, while the first is trying to select data and as such would be executed on the compute node(s).

UPDATE:

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 date for last 30 days

You are correct that this does not work on Redshift. See here.

You could use something like this

with ten_numbers as (select 1 as num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0)
,generted_numbers AS
(
    SELECT (1000*t1.num) + (100*t2.num) + (10*t3.num) + t4.num-5000 as gen_num
    FROM ten_numbers AS t1
      JOIN ten_numbers AS t2 ON 1 = 1
      JOIN ten_numbers AS t3 ON 1 = 1
      JOIN ten_numbers AS t4 ON 1 = 1
)
select  gen_num from generted_numbers
where gen_num between -10 and 0
order by 1;

You can use a window function to achieve a similar result. This requires an existing table (like stv_blocklist) to seed off that has at least the number of rows you need but not too many which might slow things down.

with days as (
    select (dateadd(day, -row_number() over (order by true), sysdate::date)) as day 
    from [other_existing_table] limit 30
)
select day from days order by 1 asc

You can use this method to get other time ranges as well for bucketing purposes. This version generates all the minutes for the previous day so you could do a left join against it and bucket your data.

with buckets AS (
    select (dateadd(minute, -row_number() over (order by true), sysdate::date)) as minute 
    from [other_table] limit 1440
)
select minute from buckets order by 1 asc

I may have first seen this here.