Using SQL Function Generate_Series() in Redshift

generate_series function in Amazon Redshift

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

How to get generate_series() function in Redshift to result in just dates NOT date-time

Just cast:

SELECT cast(current_date - i * interval '1 day' as date) as dates 
FROM generate_series(0, 7) i 
ORDER BY 1; 

Or:

SELECT (current_date - i * interval '1 day')::date as dates 
FROM generate_series(0, 7) i 
ORDER BY 1; 

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

How to cross join generate_series and table with Redshift?

You can use ROW_NUMBER() over an internal table to simulate generate_series.

date_gen AS ( 
    SELECT DATE_ADD('month', - i.i, DATE_TRUNC('month', CURRENT_DATE)) AS mnth 
    FROM (
          SELECT ROW_NUMBER() OVER() i 
          FROM stl_scan 
          LIMIT 36
         ) i
)

REDSHIFT: How can I generate a series of numbers without creating a table called numbers in redshift (Postgres 8.0.2)?

As long as you have a table that has more rows than your required series has numbers, this is what has worked for me in the past:

select
    (row_number() over (order by 1)) - 1 as hour
from
    large_table
limit 24
;

Which returns numbers 0-23.

---

Related Topics