Mysql: checking range time conflicts

Question

I have the following table where teachers class routine are stored

Table Name: batchroutine_regular

batchid class_day   subject    teacherid class_start   class_end
   1     Sunday     English-1    1001     02:00 PM     03:30 PM
   1     Sunday     Geography    1002     04:00 PM     05:30 PM
   2     Monday     Math         1001     10:00 AM     12:30 PM
   2     Tuesday    Geography    1001     01:00 PM     03:30 PM

Now lets say you are the teacher whose teacherid is 1001. You are asked to take a class on Monday which will start at 11:00 AM and end at 1:00 PM. Now my question is what would be the MYSQL/PHP query to find out if you are available at that time, from the table batchroutine_regular?

Thanks in Advance :)

Check this [similar question](http://stackoverflow.com/questions/10289675/query-for-a-list-of-multiple-day-events-occurring-between-today-and-one-week-fro). Also this question, [answered by @Erwin](http://stackoverflow.com/questions/10286561/sql-selecting-between-date-times/10286959#10286959) — ypercubeᵀᴹ, Apr 24 '12 at 12:11
Are `class_start` and `class_end` time fields? Strings? What datatype? — Nick, Apr 24 '12 at 12:12
@Thanks Nick, class_start and class_end both are varchar, not time fields. Sorry, I should have mentioned it. :) thanks :) — black_belt, Apr 24 '12 at 12:38

score 4 · Accepted Answer · answered Apr 24 '12 at 12:14

4

SELECT (COUNT(*) = 0) AS Available
FROM batchroutine_regular
WHERE
      teacherid = 1001
  AND class_day = 'Sunday'
  AND (
       (class_start <= MAKETIME(11,00,00) AND class_end >= MAKETIME(11,00,00))
    OR (class_start <= MAKETIME(13,00,00) AND class_end >= MAKETIME(13,00,00))
  )

answered Apr 24 '12 at 12:14

eggyal

122,705
18
212
237

@ eggyal Thanks for your reply. I have tried your code but its not working.. it is always showing the Available value as "1" not matter what. Could you please kindly recheck your code. Thanks again.:) – black_belt Apr 24 '12 at 12:37
1

Given your latest comment above, you might consider changing the data type of the `class_start` and `class_end` columns to [`TIME`](http://dev.mysql.com/doc/refman/5.5/en/time.html) – eggyal Apr 24 '12 at 12:42
@ eggyal. I have changed the data type to TIME but still its not working. When I changed the data type to TIME the value of my `class_start` and `class_end` columns got changed.. like this-- 02:00 AM got changed to 02:00:00. Would you please help me to find out the problem? – black_belt Apr 24 '12 at 15:15
@Srijon: Unless I'm very much mistaken 02:00:00 **is** 02:00 AM, is it not? Did your afternoon times get changed to morning ones too? If so, you'll have to manually update them to afternoon again; if you have a column containing the original data, you could do something like `UPDATE batchroutine_regular SET class_start = ADDTIME(class_start, MAKETIME(12,00,00)) WHERE old_class_start LIKE '% PM';` – eggyal Apr 24 '12 at 15:18
@ eggyal: YEs 02:00:00 is 02:00 AM. The data I had in my table was exactly like in my post above. After I have changed the data type to time 02:00 AM got changed to 02:00:00, 03:30 PM to 03:00:00 ..like this.. just `00` got replaced in place of AM/PM. Please tell me what should I do now? – black_belt Apr 24 '12 at 15:30
If the table shown in your question was complete, run these commands: `UPDATE batchroutine_regular SET class_start = ADDTIME(class_start, MAKETIME(12,00,00)), class_end = ADDTIME(class_end, MAKETIME(12,00,00)) WHERE NOT (teacherid = 1001 AND batchid = 2); UPDATE batchroutine_regular SET class_end = MAKETIME(12,30,00) WHERE teacherid = 1001 AND batchid = 2;` – eggyal Apr 24 '12 at 15:45
@ eggyal I have run the command you posted and then I tried your solution but still its showing `1` as a value of `Available`. Please check this link to see how my table is looking like after I run your command. http://i40.tinypic.com/5ldl6o.jpg And just for your information my table storage engine is InnoDB. Thanks :) – black_belt Apr 24 '12 at 16:00
Ah, my oversight - you still need to update the `class_start` time in the fourth row: `UPDATE bathroutine_regular SET class_start = MAKETIME(13,00,00) WHERE batchid = 2 AND class_day = 'Tuesday';`. And you're running the command exactly as above? Or some modified version thereof? – eggyal Apr 24 '12 at 16:03
Okay, I have updated it. I tried the script again but still now luck. Here's the complete picture of what I am doing> http://i39.tinypic.com/1z36jur.jpg :) thanks :) – black_belt Apr 24 '12 at 16:18
3

Indeed, the you are getting a result of `1` because the teacher is available during the specified hours; if you change the day/times to a cover a period in which they are not available, you will get a `0`. See http://sqlfiddle.com/#!2/43dfe/11 – eggyal Apr 24 '12 at 16:39
Sorry mistake. its working now. thanks a lot. :) – black_belt Apr 24 '12 at 16:50

Mysql: checking range time conflicts

1 Answers1

Linked