I'm building an application where the doctors have their availability time schedules and the patients can book for 30 minutes slots with the doctors for consultations. The database has two tables as doctor_availablity and booking as mentioned below.
doctor_availablity table
id | doctor_id | start_time | end_time
-----------------------------------------------------------
1 | 1 | 2020-11-27 16:00:00 | 2020-11-27 18:00:00
2 | 1 | 2020-11-27 20:00:00 | 2020-11-27 22:00:00
3 | 2 | 2020-11-27 12:00:00 | 2020-11-27 14:00:00
4 | 2 | 2020-11-27 16:00:00 | 2020-11-27 20:00:00
5 | 2 | 2020-11-27 21:00:00 | 2020-11-27 22:00:00
bookings table
id | patient_id | doctor_id | start_time | end_time
-------------------------------------------------------------------------
1 | 1 | 1 | 2020-11-27 16:15:00 | 2020-11-27 16:45:00
2 | 2 | 1 | 2020-11-27 17:00:00 | 2020-11-27 17:30:00
3 | 3 | 2 | 2020-11-27 17:00:00 | 2020-11-27 17:30:00
I need to get the available time slots for 2020-11-27 as 30 mins for a single booking. In the above scenario, the available time slots for each doctors should be
doctor_id | start_time | end_time
---------------------------------------------------------
1 | 2020-11-27 17:30:00 | 2020-11-27 18:00:00
1 | 2020-11-27 20:00:00 | 2020-11-27 22:00:00
2 | 2020-11-27 12:00:00 | 2020-11-27 14:00:00
2 | 2020-11-27 16:00:00 | 2020-11-27 17:00:00
2 | 2020-11-27 17:30:00 | 2020-11-27 20:00:00
2 | 2020-11-27 21:00:00 | 2020-11-27 22:00:00
The free times with the 30 minutes slot for each doctor needed to be taken from the above two tables. I tried several options mentioned in other questions and nothing works as in this scenario, the doctors are available in multiple timeslots on the same day itself.
Any help is appreciated.