I have a table in postgres like this:
id | open_date | close_date |
---|---|---|
5 | 2006-08-04 | 2019-12-31 |
There exist 4897 days between them. I need to turn the date range to date to have one record per day. For example:
id | open_date | close_date | valid_date |
---|---|---|---|
5 | 2006-08-04 | 2019-12-31 | 2006-08-04 |
5 | 2006-08-04 | 2019-12-31 | 2006-08-05 |
5 | 2006-08-04 | 2019-12-31 | 2006-08-06 |
… | ………. | ………. | ………. |
5 | 2006-08-04 | 2019-12-31 | 2019-12-31 |
I tried the query provided here like this:
SELECT id, open_date, close_date, open_date + seq.seqnum * interval '1 day' AS valid_date, FROM TAB1 LEFT JOIN ( SELECT row_number() over () AS seqnum FROM TAB1) seq ON seqnum <= (close_date - open_date) )
The TAB1 contains 600 rows. After running this query it produce correct records but only max 600 records for each id. This means for this date range only till 2008-06-08
Advertisement
Answer
In Postgres, you would use generate_series()
:
select t1.*, gs.valid_date from tab1 t1 cross join lateral generate_series(t1.open_date, t1.close_date, interval '1 day') as gs(valid_date);