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!

If your server version is 8+ use CTE which generates required records. If it is 5+ then use synthetic numbers table. — Akina, May 18 '20 at 19:52
@Akina That's what I am having trouble with.I am not sure how the CTE should be defined to get my desired output — jesse1990, May 18 '20 at 21:27
Consider handling issues of data display in application code — Strawberry, May 18 '20 at 21:32

score 0 · Accepted Answer · answered May 19 '20 at 01:18

0

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.

answered May 19 '20 at 01:18

Gordon Linoff

1,242,037
58
646
786

Thanks for the response. I will try it out and let you know if it works. – jesse1990 May 19 '20 at 14:44
So I tried this approach and unfortunately, SQL workbench doesn't support recursive CTE's. I might have to use another SQL engine – jesse1990 May 19 '20 at 15:52
@jesse1990 . . . MySQL has supported recursive CTEs since version 8 was released in 2018. – Gordon Linoff May 19 '20 at 21:44
I originally thought the SQL engine we were using supported MySQL but that isn't the case. That being said this does looks like the right approach to answer this question so thanks for your help! – jesse1990 May 20 '20 at 14:21

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

1 Answers1