CODEWITHSUNDEEP
phpmysql
cantaffordavan
cantaffordavan

Reputation: 1447

MySQL searching through time interval

The system is as such. Tutors provide their availability (Monday - Sunday) and the time frame they are available on that day (0700 - 1400) (ie: 7am - 2pm).

I am trying to figure out the best way to store and search through this information to find available tutors. Searching only needs to be done on a daily system (ie: day of the week - mon, tues, wed, etc).

My planned infrastructure:

//Tutor Availability
---------------------------------------------------------------------------
  tutorID  |  monday   |  tuesday  | wednesday  | thursday   |   friday   |
---------------------------------------------------------------------------
   27      | 0700-1200 |   NULL    | 1400-1800  |  NULL      |    NULL    |
---------------------------------------------------------------------------
   35      |   NULL    | 1400-1600 |    NULL    |  NULL      | 1100-1900  |

//Scheduled tutor sessions
------------------------------------
  tutorID  |    day    |  time     | 
------------------------------------
   27      |  monday   | 0700-0900 | 
------------------------------------
   35      |  friday   | 1300-1500 |

Query: SELECT tutorid FROM tutoravailability WHERE 'monday'=... is available between 0900-1100 and is not in scheduled tutor session.

I have been searching forever about how I can search through (and store) these time intervals in MySQL. Is this the best way to store the time intervals of a 24 hours day? Will it even be possible to search between these intervals? Am I approaching this from the wrong way? Any insight is appreciated.

Updated Infrastructure

//Tutor Availability
-----------------------------------------------------
  tutorID  |  day   | start_time | end_time  |  PK  | 
-----------------------------------------------------
   27      |  mon   |   0700     |   1200    |  1   |
-----------------------------------------------------
   27      |  fri   |   1400     |   1800    |  2   |
-----------------------------------------------------
   35      |  tue   |   1100     |   1600    |  3   |
//Scheduled tutor sessions
--------------------------------------------------------
  tutorID  |    day    |  start_time | end_time  |  PK |
--------------------------------------------------------
   27      |    mon    |     0800    | 1000      |  1  |
--------------------------------------------------------
   27      |    fri    |     1600    | 1800      |  2  |

So with this system it will be much simpler to search for available times. However I am still at a loss as to how to compare the availability against the scheduled lessons to ensure no overlap.

SELECT tutorID 
FROM tutoravailability WHERE day = 'fri' 
AND start_time <= '1400' 
AND end_time >= '1530'

Now I don't understand how I would compare this query against the Scheduled tutor sessions table to avoid duplicate bookings.

Final Update

To ensure their are no overlapping of the Scheduled Tutors sessions I will use the MySQL BETWEEN clause to search for the start and end time.

Upvotes: 2

Views: 482

Answers (2)

AnthonyY
AnthonyY

Reputation: 721

You can use a bit flag to indicate the availability (24 bit) and scheduled time (24 bit). Then you can use 24 bit to represent the available hours and scheduled hours for each day.

In the Tutor Availability table, let's say '1' stands for Available in and '0' stands for unavailable. In the Scheduled table, '0' stands for Scheduled, '1' stands for Unscheduled.

So the available interval 0900-1100 can be stored as POW(2,9) | POW(2,10) | POW(2,11); the scheduled 1000-1200 can be stored as ^(POW(2,10) | POW(2,12))

Then the following query can give your the availability of on tutor - available on Monday between 09 am to 11 am:

SELECT ta.tutorid FROM tutoravailability ta, tutorscheduled ts 
WHERE ta.tutorid = ts.tutorid AND ts.day = 'monday' 
      AND (ta.monday & ts.time & (POW(2,9) | POW(2,10) | POW(2,11))) = (POW(2,9) | POW(2,10) | POW(2,11))

Upvotes: 0

Bowie
Bowie

Reputation: 1002

If you store the time interval using two columns it will be much easier for you to perform a search using sql query.

i.e. tutorID, day, startTime, endTime

Upvotes: 4

Related Questions