postgresqldatedate-rangedateinterval

postgresql generating a list of dates between two dates fields

I would like to get the list of dates from two fields: start and end. I found a case here: Show a list of dates between two dates?

But I would like to have a better solution without going through an intermediary table.

Here is the initial table:

enter image description here

Here is the result I would like to have:

enter image description here

Thank you

Solution

  • Use generate_series()

    select t.id, t.name, t.g.dt::date as start_end
from the_table t
  cross join generate_series(t.date_start, t.date_end, interval '1 day') as g(dt)
order by t.id, g.dt;