0

I am trying to generate a list of dates on Redshift to join it with another table with a start and end date of events so that I can have a list of event ids for each date.

This query produces the list of dates:

select date '2023-03-01' + i AS dt
from  generate_series(0, ((current_date - 5) - date '2023-03-01')) i)
dt
2023-03-01
2023-03-02
2023-03-03
...

This looks into the events table:

select e_id, start_date, end_date
from  tableA
e_id start_date end_date
1 2023-03-01 2023-03-04
2 2023-03-03 2023-03-05
3 2023-03-02 2023-03-03

I have tried this query:

with dl as (
select date '2023-03-01' + i AS dt
from  generate_series(0, ((current_date - 5) - date '2023-03-01')) i)
)
, e as (
select e_id, start_date, end_date
from  tableA
)
select dt, e_id
from dl
left join e on dl.dt >= e.start_date and dl.dt <= e.end_date

but I get the following error:

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

Given the examples above, I would expect the following result:

dt e_id
2023-03-01 1
2023-03-02 1
2023-03-02 3
2023-03-03 1
2023-03-03 2
2023-03-03 3
2023-03-04 1
2023-03-04 2
2023-03-05 2
Kiara
  • 1
  • 1

1 Answers1

1

generate_series() is not supported as a means to make data for use against your table data. There are many answers to why (hardware architecture) and using a recursive CTE to generate the same results. One such recursive CTE to generate date from a previous answer:

trying to create a date table in Redshift

==========================================================

Again from a previous answer (but much older): You can generate an arbitrary sequence of number using binary power approach and cross-joining. See:

Redshift: Generate a sequential range of numbers

Once the sequence of necessary numbers are created you use this to add days to a starting date up until your necessary end date.

Bill Weiner
  • 8,835
  • 2
  • 7
  • 18
  • Thank you @bill-weiner ! This works for getting the desired output. However, unfortunately I cannot use recursive CTEs because I have to enter the query into a third party server that does not support recursive CTEs for Redshift connections. Is there really no way of producing a list of dates in Redshift without a recursive CTE? – Kiara Aug 11 '23 at 08:37
  • Recursive CTEs are fairly new in Redshift so yes, there are older methods for doing this. I'm assuming you need a query that will work in both databases so I'll pick one that should do this. I'll update my answer with a binary math method that should meet the need. – Bill Weiner Aug 11 '23 at 15:03