Complex logic to create time series in Postgres-CodePudding

I have a sample dataset like below and I would like to create a report in such a format that the Value is updated for all the dates between the Start and End date.

Input Dataset

ID      Start                  End                     Value
232      "2022-06-08 18:49:00"  "2022-11-18 08:06:00"   55
456      "2022-10-17 10:24:00"  "2022-12-16 12:52:00"   100

From the above Dataset I would like to create another dataset as below. I need to generate the date series from the START and END date from the Input dataset and fill the same value to all of those value. Any ideas or suggestions will be helpful.

Expected Output

ID      Date           Value
 232      "2022-06-08"  55
 232      "2022-06-09"  55
 232      "2022-06-10"  55
 232      "2022-06-11"  55
 232      "2022-06-12"  55
 .
 .
 232      "2022-11-17"  55
 232      "2022-11-18"  55
 456      "2022-10-17"  100
 456      "2022-10-18"  100
 456      "2022-10-19"  100
 .
 .
 456      "2022-12-15"  100
 456      "2022-12-16"  100

Database : Postgres 12

CodePudding user response：

You can use generate_series()

select t.id,
       g.dt::date as date,
       t.value
from the_table t       
  cross join generate_series(t."Start"::date, t."End"::date, interval '1 day') as g(dt)
order by t.id, g.dt