Generate_series function in Amazon Redshift

Question

I tried the following:

SELECT * FROM generate_series(2,4); generate_series ----------------- 2 3 4 (3 rows) SELECT * FROM generate_series(5,1,-2); generate_series ----------------- 5 3 1 (3 rows) 

But when I try

 select * from generate_series('2011-12-31'::timestamp, '2012-12-31'::timestamp, '1 day'); 

This caused an error.

 ERROR: function generate_series(timestamp without time zone, timestamp without time zone, "unknown") does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. 

I am using PostgreSQL 8.0.2 in Redshift 1.0.757.
Any idea why this is happening?

UPDATE:

Now generate_series works with Redshift.

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

This will generate the last 30 days

Answer 1

Now generate_series works with Redshift.

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

This will generate the last 30 days

Answer 2

A version of generate_series() , which supports dates and timestamps, was added in Postgres 8.4.

Since Redshift is based on Postgres 8.0, you need to use a different method:

 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 shortened to:

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

Answer 3

Redshift does not have a generate_series() function for the date range, but you can generate a series using the following steps ...

Step 1: Create a genid table and insert a constant value as 1 for the number of times you need to create the series. If you need a series to be created within 12 months, you can insert 12 times. You can better insert a few more times, for example, 100, so that you do not encounter any problem.

 create table genid(id int) 

------------ for the number of months of insertion into the values ​​of geniuses (1)

Step 2: The table for which you need to create a series.

 create table pat(patid varchar(10),stdt timestamp, enddt timestamp); insert into pat values('Pat01','2018-03-30 00:00:00.0','2018-04-30 00:00:00.0') insert into pat values('Pat02','2018-02-28 00:00:00.0','2018-04-30 00:00:00.0') insert into pat values('Pat03','2017-10-28 00:00:00.0','2018-04-30 00:00:00.0') 

Step 3: This query will generate a series for you.

 with cte as ( select max(enddt) as maxdt from pat ) , cte2 as( select dateadd('month', -1 * row_number() over(order by 1), maxdt::date ) as gendt from genid , cte ) select * from pat, cte2 where gendt between stdt and enddt 

Answer 4

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

 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 ad + (10 * bd) + (100 * cd) + (1000 * dd) 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 does not seem to be fully supported in Redshift yet. If I run the SQL mentioned in DJo's answer, it works because SQL only works on the master node. If I add the insert in dim_time to the same SQL, it does not work.