List:General Discussion« Previous MessageNext Message »
From:C.F. Scheidecker Antunes Date:October 27 2005 9:31pm
Subject:MySQL and dates puzzle
View as plain text  
Hello all,

I have a TA table to record TA UNAVAILABLE times.
This table is quite simple. It has a TAID number, a start date and an 
end date.

tbl_schedule {
TAID  integer,
starts  datetime,
ends datetime }

A valid entry would be a TA whose id is 1 and between 17:00 and 18:00 he 
is busy.
So:
1,'2005-10-27 17:00:00','2005-10-27 18:00:00'

Each ta can have more than one entry per day. He might be a busy TA and 
have a lot
of meetings scheduled. The meetings do not have to be 1 hour length, 
they can be 5 or 10 minutes.
So something like this would also be valid:
1,'2005-10-27 17:05:00','2005-10-27 17:10:00'

Now, I need to check, given a start and end dates, if that would overlap 
with some record already present
in the database.
If I want to know if the TA is busy between 17:30 and 18:10 I could I 
issue something like this:

SELECT count(TAID) as total FROM tbl_schedule where (TAID = 1) AND 
(('2005-10-27 17:30' BETWEEN starts AND ends) OR ('2005-10-27 18:10' 
BETWEEN starts AND ends))

It would return a number not zero as total if the dates are between the 
registered database. However, this does not work properly.
Here's a case when it does not work:
Say I want to check between 14:00 and 20:00. The TA is busy from 17:00 
and 18:00, hence if I try to schedule a meeting that will go
from 14:00 to 20:00 with the statement above it would return 0 as total. 
This is not good because I need to know that given 14:00 to 20:00 that would
not overlap with any previous engagement on the database. Since the TA 
is busy from 17:00 to 18:00 I must know that I cannot schedule anything like
that.

Can anyone help me on this issue? How can I check given a start and end 
datetime that it does not overlap with what is in the database?

Thanks,

C.F.

Thread
MySQL and dates puzzleC.F. Scheidecker Antunes27 Oct
  • Re: MySQL and dates puzzleMichael McFadden28 Oct
    • Re: MySQL and dates puzzleSGreen28 Oct
      • Re: MySQL and dates puzzleMichael McFadden28 Oct
  • Re: {Spam?} MySQL and dates puzzleddevaudreuil28 Oct