CODEWITHSUNDEEP
sqlamazon-redshift
Heisenberg
Heisenberg

Reputation: 1508

Amazon Redshift: How to create a table containing time series

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?

Upvotes: 1

Views: 1567

Answers (1)

jbm
jbm

Reputation: 2600

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
...

Upvotes: 4

Related Questions