Date range to row in postgres

Question

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

Gordon Linoff · Accepted Answer · 2021-08-20 13:45:21Z

6

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);

answered Aug 20, 2021 at 13:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Date range to row in postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related