How to program schedule and available times?

Question

PHP

Hello.
There is a system of dentistry offices and doctors themselves, each doctor has a table with available time (hours from 3-00 to 15-00, etc. by day of the week, repeating) and a table in the database where client appointments are recorded (in the format start time (timestamp) and end (timestamp).How to store these same hours and days of availability so that they can be used with such tables when building a schedule (busy \ free) time? Thanks for any advice)

Answer 1

I think that time quantization can help here, i.e. division into minimum segments, for example, the reception should be a multiple of 10 minutes minimum. Next, we select by request the time when the doctor works and all the records for this day to this doctor, by pieces of the busy time we already create a table of free intervals (for example, we make the cells with the busy time red and the rest green). Further on the desired functionality.
There are SQL queries with the between condition.
select starttime, endtime, pacient from Pacients where `starttime` between '2016/02/25 03:00:00.000' and '2016/02/25 15:00:00.000'
will return an array with occupied intervals.