Skip to content
Advertisement

Date range to row in postgres

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);
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement