Amazon Redshift: How to create a table containing time series

Question

I want to create a table in Redshift which will have a column date whose values will be from today to next 3 years.

        date         
---------------------
 2017-05-03 00:00:00
 2017-05-04 00:00:00
 2017-05-05 00:00:00
 2017-05-06 00:00:00

I am trying to do this using a create table as select_query statement.

 create table tmp_date_series as select now()::date::timestamp + generate_series(0, 1000);

However, the above query fails with an error -

INFO:  Function "now()" not supported.
INFO:  Function "generate_series(integer,integer)" not supported.
ERROR:  Specified types or functions (one per INFO message) not supported on Redshift tables.

But if I run the select query alone - select now()::date::timestamp + generate_series(0, 150) as date;, it runs without any error-

        date         
---------------------
 2017-05-03 00:00:00
 2017-05-04 00:00:00
 2017-05-05 00:00:00

Any idea how do I create such a table?

Voting to reopen, `generate_series` is not available on AWS Redshift. — Marth, May 03 '17 at 20:38
Shouldn't `generate_series()` go into the `from` clause? `create table x as select (current_date + i)::timestamp from generate_series(0,1000) as x(i)` — , May 03 '17 at 20:41
As an aside, the query in your question that actually works only does because it runs solely on the leader node, which uses a fork of PostgreSQL 8.4. As soon as you hit the worker nodes (for instance if you use any 'real' table), the query runs on those, which do not have `generate_series`. — Marth, May 03 '17 at 20:42
Instead of `generate_series` you have to use a table with numbers or a cte. http://stackoverflow.com/questions/38667215/redshift-how-can-i-generate-a-series-of-numbers-without-creating-a-table-called — Juan Carlos Oropeza, May 03 '17 at 20:43
https://stackoverflow.com/questions/17282276/using-sql-function-generate-series-in-redshift — Juan Carlos Oropeza, May 03 '17 at 20:45
@marth Looks like postgres support it [now](http://stackoverflow.com/a/33577864/3470178) but im not in position to test it. — Juan Carlos Oropeza, May 03 '17 at 20:48

jbm · Accepted Answer · 2019-04-05T18:10:19.420

Unfortunately, generate_series isn't supported in Redshift.

My team is using a CTE like this to get a series of consecutive dates starting from a particular date:

with dates as (
    select
        (trunc(getdate()) + row_number() over (order by 1))::date as date
    from
        large_enough_table
    limit
        150
)

Then you can use it as:

select date
from dates
order by date
limit 5

And get:

2018-12-13
2018-12-14
...

Amazon Redshift: How to create a table containing time series

1 Answers1