473,324 Members | 2,124 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

how to check for date/time ranges within record (check for schedule conflicts)

You want to check scheduling conflicts and you have a record like:

appointments(table):
apptID
beginningDate
endingDate
beginningTime
endingTime

It's easy enough to check if a time is within that record. Say you want to
check if 8:00am to 10:00am is available, you would use this:

SELECT apptID
FROM appointments
WHERE (beginningDate = '2006-01-19' OR endingDate = '2006-01-19')
AND ('08:00:00' BETWEEN beginningTime AND endingTime
OR '10:00:00' BETWEEN beginningTime AND endingTime)

BUT, what if you have an all-day appointment (8:00am to 5:00pm) and there
exists an appointment already scheduled from 10:00am to 11:00am. The above
query would not find it.

Another question is what if the appointment is more than two days. Say, it's
from Monday - Wednesday from 8am to 5pm. The above query would not
successfully catch it if you wanted to schedule an appointment on Tuesday.
(I might be able to generate a date range using PHP, don't know if that's
the best way)

Any ideas? Thank you very much for any help!

PS: I am using MySQL 3.23.55 and PHP4.x
Jan 20 '06 #1
5 9392
Notgiven wrote:
You want to check scheduling conflicts and you have a record like:

appointments(table):
apptID
beginningDate
endingDate
beginningTime
endingTime
I might have created the table differently :-)

appointments(table):
apptID
beginningDateTime
endingDateTime
It's easy enough to check if a time is within that record. Say you want to
check if 8:00am to 10:00am is available, you would use this:

SELECT apptID
FROM appointments
WHERE (beginningDate = '2006-01-19' OR endingDate = '2006-01-19')
AND ('08:00:00' BETWEEN beginningTime AND endingTime
OR '10:00:00' BETWEEN beginningTime AND endingTime)
SELECT apptID
FROM appointments
WHERE (beginningDateTime BETWEEN 20060119080000 AND 20060119100000)
OR (endingDateTime BETWEEN 20060119080000 AND 20060119100000)
OR (20060119090000 /* could be anything between 8am and 10am */
BETWEEN beginningDateTime AND endingDateTime)
BUT, what if you have an all-day appointment (8:00am to 5:00pm) and there
exists an appointment already scheduled from 10:00am to 11:00am. The above
query would not find it.

Another question is what if the appointment is more than two days. Say, it's
from Monday - Wednesday from 8am to 5pm. The above query would not
successfully catch it if you wanted to schedule an appointment on Tuesday.
(I might be able to generate a date range using PHP, don't know if that's
the best way)

Any ideas? Thank you very much for any help!


Maybe this other structure raises other problems not specified in your
article ...

HTH

--
If you're posting through Google read <http://cfaj.freeshell.org/google>
Jan 20 '06 #2
"Notgiven" <no*********@invalid.invalid> wrote in message
news:rw***************@bignews7.bellsouth.net...
SELECT apptID
FROM appointments
WHERE (beginningDate = '2006-01-19' OR endingDate = '2006-01-19')
AND ('08:00:00' BETWEEN beginningTime AND endingTime
OR '10:00:00' BETWEEN beginningTime AND endingTime)

BUT, what if you have an all-day appointment (8:00am to 5:00pm) and there
exists an appointment already scheduled from 10:00am to 11:00am. The above
query would not find it.
Well, you'd need more terms in your condition. See the book "Joe Celko's
SQL for Smarties", specifically section 12.2 on testing for overlapping time
intervals.
Another question is what if the appointment is more than two days. Say,
it's from Monday - Wednesday from 8am to 5pm. The above query would not
successfully catch it if you wanted to schedule an appointment on Tuesday.


Right -- you have complex schedules, so your conditions need to be complex.
Something like this might be closer to what you need:

SELECT . . .
WHERE '2006-01-19' BETWEEN beginningDate AND endingDate
AND ('08:00:00' BETWEEN beginningTime AND endingTime
OR '17:00:00' BETWEEN beginningTime AND endingTime
OR beginningTime BETWEEN '08:00:00' AND '17:00:00'
OR endingTime BETWEN '08:00:00' AND '17:00:00'
)

Regards,
Bill K.
Jan 21 '06 #3
"Pedro Graca" <he****@dodgeit.com> wrote in message
news:sl*******************@ID-203069.user.individual.net...
Notgiven wrote:
You want to check scheduling conflicts and you have a record like:

appointments(table):
apptID
beginningDate
endingDate
beginningTime
endingTime


I might have created the table differently :-)

appointments(table):
apptID
beginningDateTime
endingDateTime
It's easy enough to check if a time is within that record. Say you want
to
check if 8:00am to 10:00am is available, you would use this:

SELECT apptID
FROM appointments
WHERE (beginningDate = '2006-01-19' OR endingDate = '2006-01-19')
AND ('08:00:00' BETWEEN beginningTime AND endingTime
OR '10:00:00' BETWEEN beginningTime AND endingTime)


SELECT apptID
FROM appointments
WHERE (beginningDateTime BETWEEN 20060119080000 AND 20060119100000)
OR (endingDateTime BETWEEN 20060119080000 AND 20060119100000)
OR (20060119090000 /* could be anything between 8am and 10am */
BETWEEN beginningDateTime AND endingDateTime)
BUT, what if you have an all-day appointment (8:00am to 5:00pm) and there
exists an appointment already scheduled from 10:00am to 11:00am. The
above
query would not find it.

Another question is what if the appointment is more than two days. Say,
it's
from Monday - Wednesday from 8am to 5pm. The above query would not
successfully catch it if you wanted to schedule an appointment on
Tuesday.
(I might be able to generate a date range using PHP, don't know if that's
the best way)

Any ideas? Thank you very much for any help!


Maybe this other structure raises other problems not specified in your
article ...

HTH

--
If you're posting through Google read <http://cfaj.freeshell.org/google>


Thanks. However, if you have a single event scheduled from 8am to 10am on
Mon, Tues, Wed, it would practically place a hold on everything between 8am
Monday and 10a, Wed. This, it would would not allow me to schedule a 2pm
Tuesday event.
Jan 23 '06 #4

"Bill Karwin" <bi**@karwin.com> wrote in message
news:dq********@enews1.newsguy.com...
"Notgiven" <no*********@invalid.invalid> wrote in message
news:rw***************@bignews7.bellsouth.net...
SELECT apptID
FROM appointments
WHERE (beginningDate = '2006-01-19' OR endingDate = '2006-01-19')
AND ('08:00:00' BETWEEN beginningTime AND endingTime
OR '10:00:00' BETWEEN beginningTime AND endingTime)

BUT, what if you have an all-day appointment (8:00am to 5:00pm) and there
exists an appointment already scheduled from 10:00am to 11:00am. The
above query would not find it.


Well, you'd need more terms in your condition. See the book "Joe Celko's
SQL for Smarties", specifically section 12.2 on testing for overlapping
time intervals.


Know where I can see a copy or these pages?
Another question is what if the appointment is more than two days. Say,
it's from Monday - Wednesday from 8am to 5pm. The above query would not
successfully catch it if you wanted to schedule an appointment on
Tuesday.


Right -- you have complex schedules, so your conditions need to be
complex.
Something like this might be closer to what you need:

SELECT . . .
WHERE '2006-01-19' BETWEEN beginningDate AND endingDate
AND ('08:00:00' BETWEEN beginningTime AND endingTime
OR '17:00:00' BETWEEN beginningTime AND endingTime
OR beginningTime BETWEEN '08:00:00' AND '17:00:00'
OR endingTime BETWEN '08:00:00' AND '17:00:00'
)

Regards,
Bill K.


That sql code worked perfect - thanks!
Jan 23 '06 #5
"Notgiven" <no*********@invalid.invalid> wrote in message
news:LH*****************@bignews4.bellsouth.net...
Well, you'd need more terms in your condition. See the book "Joe Celko's
SQL for Smarties", specifically section 12.2 on testing for overlapping
time intervals.


Know where I can see a copy or these pages?


In this case, you can also preview some of the relevant pages using
Amazon.com's "search inside the book" feature. The 3rd edition moves the
content I mentioned to chapter 13, pp. 275-286. Looks like pp. 277-281 are
viewable online via:
http://www.amazon.com/gp/product/0123693799/

I recommend purchasing this book, it's full of useful topics.

Regards,
Bill K.
Jan 23 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: vishal | last post by:
i want to store the date and time when the user registered i.e. the record is entered in my table. so how can i achieve this???? should i use php or mysql to achieve this?? which is more...
3
by: jason | last post by:
I need to deposit and empty dummy value into a date/time field within Access using INSERT. However, I pick up a data type error if I attempt to insert a NULL value for this particular date time...
5
by: Gord | last post by:
Many scripts and calendars call client side system time in order to make presentations. However, the client's time may be improperly set, if set at all, and/or the relevant time may be from...
17
by: Lapchien | last post by:
My table has a record called date/time - filled (not surprisingly) with a date and time. I can format the date in one query field, also the time, using format in an expression. My user wants me...
4
by: Joe User | last post by:
Hi all....I have a feeling this is going to be one of those twisted query questions, but here it goes anyways.... I want to generate a report that shows the chronology of events (represented by...
2
by: Tu Quach | last post by:
Hi there, how are you? I have question, assume I have a text box and gave it a format date&time, how can I check when it is empty or not empty I use the code as if frm_form!txt_name1 = Null...
5
by: Notgiven | last post by:
You want to check scheduling conflicts and you have a record like: appointments(table): apptID beginningDate endingDate beginningTime endingTime It's easy enough to check if a time is...
3
by: phried1 | last post by:
I have created a form and inserted the following tables: Date Entered Time Entered Date Modified Time Modified Essentially how and where can I have these dates and times recorded so when the...
2
by: jamil616 | last post by:
Hi all, I have a form which is also built with a subform. In the form I add the date and time when a record is changed/added to a respective table in the database. So that, when the next...
2
dbrewerton
by: dbrewerton | last post by:
I'm kind of at a loss as to how to do this but I'm going to run it by you experts anyway. I'm looking to create a linechart that will specify information based on the following ranges: Last 8...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.