Showing a row for every minute based on start and end time

Question

I have a table that looks like below:

task_id          start_date               end_date
  t1          2020-05-01 8:00:00    2020-05-01 9:45:00
  t2          2020-05-01 8:30:00    2020-05-01 9:00:00
  t3          2020-05-01 8:45:00    2020-05-01 9:30:00

I want my SQL output to display a row for every minute of a task that has passed based on the start and end date. So, for example, t1 should look like

task_id     time
  t1        2020-05-01 8:00:00
  t1        2020-05-01 8:01:00
  t1        2020-05-01 8:02:00
  t1        2020-05-01 8:03:00
.....   .....
  t1        2020-05-01 9:45:00

Similarly, t2 would look like

task_id      time
  t2    2020-05-01 8:30:00
  t2    2020-05-01 8:31:00
  t2    2020-05-01 8:32:00
  t2    2020-05-01 8:33:00
.....   .....
  t2    2020-05-01 9:00:00

I was looking at this thread and tried to mimic it but I wasn't able to produce the desired result.

Any and all help will be appreciated.

Thank you all!

Answer 1

The recursive CTE looks like this:

with recursive cte as (
      select task_id, start_date, end_date
      from t
      union all
      select task_id, start_date + interval 1 minute, end_date
      from cte
      where start_date < end_date
     )
select task_id, start_date
from cte;

Here is a db<>fiddle.