Assuming you have a table with a start_time and end_time column (timestamps in my examples), something like this:
mysql> select * from test;
+----------------+----------------+
| start_time | end_time |
+----------------+----------------+
| 20010203120000 | 20010203150000 |
| 20010203080000 | 20010203093000 |
| 20010203180000 | 20010203183000 |
+----------------+----------------+
3 rows in set (0.05 sec)
That shows 3 meetings, 0800-0930, 1200-1500, 1800-1830, all on feb 3, 2001.
Construct your queries like this:
Trial 1. Someone wants to schedule a meeting from 1500-1800. No rows are returned, so that means it's a valid meeting time:
mysql> select * from test where start_time + interval 1 second between '20010203
150000' and '20010203180000' or end_time - interval 1 second between '2001020315
0000' and '20010203180000';
Empty set (0.05 sec)
Trial 2: Someone wants to schedule a meeting from 1100-1215. Shouldn't work, since a meeting already starts at 1200. When it returns a row, that means the meeting time is no good.
mysql> select * from test where start_time + interval 1 second between '20010203
110000' + interval 0 second and '20010203121500' + interval 0 second or end_time
- interval 1 second between '20010203110000' + interval 0 second and '200102031
21500' + interval 1 second;
+----------------+----------------+
| start_time | end_time |
+----------------+----------------+
| 20010203120000 | 20010203150000 |
+----------------+----------------+
1 row in set (0.00 sec)
I had to add the "+ interval 0 second" in the second query to make sure the strings were compared as dates, not strings...You'd probably want to use it in all of your queries.
I added/subtracted the second from start_time/end_time to account for someone ending a meeting at 1200, and someone wanting to start a meeting at 1200, which should be valid. You might want to create a 5-10 minute buffer around the start_time and end_time before you even put it in the database, to account for the changing of personnel...up to you...
So your query would be:
select * from test where start_time + interval 1 second between '$start_time' + interval 0 second and '$end_time' + interval 0 second or end_time - interval 1 second between '$start_time' + interval 0 second and '$end_time' + interval 1 second;
To create a buffer, you'd use the same query, except when you insert the $start_time and $end_time, add the buffer:
//Buffer == 10
INSERT INTO test (start_time,end_time) VALUES ('$start_time' + INTERVAL 10 MINUTE,'$end_time' + INTERVAL 10 MINUTE');
Let me know if that works for you.
Another option is to install the Mcal library, which is made for calender stuff like this. This would be very easy...it accounts for reoccuring events, also. It's basically a database made specifically for manipulating calender and date functions...
---John Holmes...
pranot wrote:
thanks... jan-mike
yeah... i have understood the logic...
but will mysql checks the greater and lesser time...
i mean
can it check 3:15 greater than 3:00 in database.
or
3:00 is lesser than 3:15.
also i how should i declare the fields in database....
whether i should give
create table tablename (
fromdate date,
todate date,
fromtime time,
totime time );
or
some thing other.....
pls let me know...
thanks,
pranot
