Query to find time window between visits

Question

I'm creating a small app to register visits and I've got stuck on finding free time windows between visits.

There are two tables, this is a simplified structure:

working_hours
- start_time
- end_time

visit
- start_time
- end_time
- visit_status

visits_status with value "2" are cancelled visits so we do not include them

Now small example:

Employee has his own working hours with breaks included, for example:

| start_time          | end_time            |
| 2018-12-29 08:00:00 | 2018-12-29 12:00:00 |
| 2018-12-29 12:30:00 | 2018-12-29 16:00:00 |

There are already visits registered in app, they may have different durations. Visit times have got included break so next visit can start right after . Let's say we have visits like those:

| start_time          | end_time            | visit_status |
| 2018-12-29 08:00:00 | 2018-12-29 08:30:00 | 1            |
| 2018-12-29 09:00:00 | 2018-12-29 10:00:00 | 1            |
| 2018-12-29 10:00:00 | 2018-12-29 10:40:00 | 1            |
| 2018-12-29 10:40:00 | 2018-12-29 11:10:00 | 2            |
| 2018-12-29 11:10:00 | 2018-12-29 11:40:00 | 0            |
| 2018-12-29 12:30:00 | 2018-12-29 13:00:00 | 0            |
| 2018-12-29 13:00:00 | 2018-12-29 14:00:00 | 0            |
| 2018-12-29 15:30:00 | 2018-12-29 16:00:00 | 0            |

My goal is to create query that will show me available start times for thirty minutes visit including working hours, In this specified case the result should be this times:

• 8:30
• 10:40
• 14:00
• 14:30
• 15:00

Answer 1

This doesn't look like a straightforward gaps-and-islands problem.

First there's the issue of finding the gaps between the visits that are big enough.
And this means that both the start & end times need to be considered.

Then when those gaps are found, they need to be unfolded in 30 minute intervals.

To unfold the gaps you can link to a number table.
It's best to create a permanent one.
The example below just adds values, to keep it simple for this answer.
But there are other methods. F.e. here

create table nums (num int primary key not null);
insert into nums (num) VALUES 
(00),(01),(02),(03),(04),(05),(06),(07),(08),(09),
(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
(40),(41),(42),(43),(44),(45),(46),(47),(48),(49);

Then a query like this will work in MySql 5.7

SELECT DISTINCT 
 CAST(gaps.start_dt + INTERVAL (nums.num * 30) MINUTE AS TIME) as start_time
FROM
(
  SELECT rnk, MIN(prev_dt) as start_dt, MIN(start_dt) as end_dt
  FROM
  (
    SELECT 
    start_time AS start_dt, 
    end_time as end_dt, 
    @prev_dt as prev_dt,
    -- DATE(@prev_dt) + INTERVAL (CEIL(TIME_TO_SEC(@prev_dt) / 600) * 600) SECOND as prev_dt,
    (CASE
     WHEN @prev_dt = start_time AND @prev_dt := end_time THEN @rnk
     WHEN @prev_dt := end_time THEN @rnk := @rnk + 1
     END) AS rnk
    FROM visits
    CROSS JOIN (SELECT @prev_dt := null, @rnk := 0) vars
    WHERE visit_status <> 2
    ORDER BY start_time
  ) AS vst
  GROUP BY rnk
  HAVING CAST(MIN(start_dt) AS DATE) = CAST(MIN(prev_dt) AS DATE)
     AND TIMEDIFF(MIN(start_dt), MIN(prev_dt)) >= CAST('00:30' AS TIME)
) gaps
JOIN working_hours wrk 
  ON wrk.start_time <= gaps.start_dt AND wrk.end_time >= gaps.end_dt
JOIN nums
  ON nums.num BETWEEN 0 AND 47
 AND gaps.start_dt + INTERVAL (nums.num * 30) MINUTE < gaps.end_dt;

The "vst" sub-query assigns a ranking to the datetimes by using variables.

Then the "gaps" sub-query groups them by that ranking to find the start & end of the gaps.

Then the working_hours is joined.
And the "nums" table is used to unfold the times in 30 minute intervals.

Result:

start_time
08:30:00
10:40:00
14:00:00
14:30:00
15:00:00

You can test it on db<>fiddle here