Search code examples
postgresqlgenerate-series

Generating dates for each id

I have a table with 3 columns. I want to create a date column for each id from its start_date till end_date, if no end_date then till today.

How can I active this in Postgres? Thanks!

Sample table:

+------+------------+------------+--+
|  id  | start_date |  end_date  |  |
+------+------------+------------+--+
| 47ef | 2020-09-25 | 2020-09-30 |  |
| b67c | 2020-09-21 | 2020-10-02 |  |
| 9f9e | 2020-08-28 | 2020-10-02 |  |
| 854a | 2020-07-29 | 2020-10-05 |  |
| a316 | 2020-05-01 | NULL       |  |
+------+------------+------------+--+

Desired output for id '47ef':

+------+------------+------------+------------+
|  id  | start_date |  end_date  |    date    |
+------+------------+------------+------------+
| 47ef | 2020-09-25 | 2020-09-30 | 2020-09-25 |
| 47ef | 2020-09-25 | 2020-09-30 | 2020-09-26 |
| 47ef | 2020-09-25 | 2020-09-30 | 2020-09-27 |
| 47ef | 2020-09-25 | 2020-09-30 | 2020-09-28 |
| 47ef | 2020-09-25 | 2020-09-30 | 2020-09-29 |
| 47ef | 2020-09-25 | 2020-09-30 | 2020-09-30 |
+------+------------+------------+------------+
Solution

  • You can indeed use generate_series():

    select t.id, t.start_date, t.end_date, g.dt::date
from the_table t
    cross join lateral generate_series(t.start_date, coalesce(t.end_date, current_date), interval '1 day') as g(dt)
order by t.id, g.dt