Question 118703 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Get avalable time from 2 tables

I have 2 tables: availability and blocked_availability.

availability:

day start end
1 09:00 11:00
1 12:00 16:00

blocked_availabilty

day start end
1 09:30 10:30
1 12:30 14:30

From these 2 tables combined I would like to see if there’s a period of at least X minutes of availability.

In above example there some available timeslots:

09:00 – 09:30

10:30 – 11:00

12:00 – 12:30

14:30 – 16:00

Is there a query to see if there’s an available timeslot of at least 60 minutes (there’s only 1 in this example).

Tags:

3

Answers


  1. 0

    You can use a recursive cte to build timeslot intervals from the availability listings and then left join the blocked availabilties to the cte. Then, a ranking can performed on the rows that do not have a match in the blocked availabilities (thus it is available), and the rank values can be grouped on to find the total number of minutes per interval:

    with recursive cte(r, day, min, end) as (
   select row_number() over (order by a.day), a.day, a.start, a.end from availability a
   union all
   select c.r, c.day, c.min + interval 1 minute, c.end from cte c where c.min < c.end
),
slots as (
   select c.day, c.r, c.min, c.end, a.start is null av from cte c 
   left join blocked_availability a on c.day = a.day and a.start < c.min and c.min < a.end
   order by c.end
),
a_slots as (
   select (select sum(s1.day = s.day and s1.min < s.min and s1.av != s.av) from slots s1) r1, s.r, s.day, s.min, s.av from slots s
)
select t.m1, t.m2 from (select a.r1, a.r, min(a.min) m1, max(a.min) m2 
    from a_slots a where a.av group by a.r1, a.r) t
where time_to_sec(timediff(t.m2, t.m1)) / 60 >= 60 -- here, specifying the number of minutes the interval must be

    See fiddle

    Login or Signup to reply.
  2. 0

    Here’s an example query that you could use to find available timeslots of at least 60 minutes:

    SELECT a.day, a.end AS start, b.start AS end
FROM availability AS a
JOIN availability AS b ON a.day = b.day AND a.end <= b.start
LEFT JOIN blocked_availability AS ba ON a.day = ba.day AND a.end >= ba.start AND b.start <= ba.end
WHERE b.start - a.end >= X AND ba.day IS NULL

    In this query, X is the minimum number of minutes for an available timeslot (in this case, 60 minutes).

    The query works by joining the availability table with itself to find pairs of time slots that are adjacent on the same day. The LEFT JOIN with blocked_availability table excludes any time slots that overlap with blocked time slots. Finally, the WHERE clause filters the results to include only time slots that are at least X minutes long and do not overlap with blocked time slots.

    Note that you’ll need to replace X with the number of minutes you want to check for, and adjust the table and column names as needed to match your schema.

    Login or Signup to reply.
  3. 0

    Unpivot values from both tables (here done with union all), then use lead(). This produces all possible periods from your tables. Now you can join with original data, filter "blocked" periods and count time difference.

    with t(day, t1, t2) as (
  select day, t1, lead(t1) over (order by t1) t2 
  from (
    select day, start t1 from availability union all
    select day, end   from availability union all
    select day, start from blocked_availability union all
    select day, end   from blocked_availability ) upvt )
select t.day, t.t1, t.t2, 
       timediff(str_to_date(t.t2, '%H:%i'), str_to_date(t.t1, '%H:%i')) diff 
from t
join availability a 
  on a.day = t.day and t.t1 < a.end and a.start < t.t2
left join blocked_availability b 
  on b.day = t.day and t.t1 < b.end and b.start < t.t2
where b.day is null 
order by t.day, t.t1

    dbfiddle demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search