Date Differnces

Icy5000

Hi,
I have a the following problem:
I'm building a little tool in order to manage rooms. There the user has the possibility to select and block a room for a defined timespace.
For that I have a DB-Table with the fields DateStart, DateEnd, TimeStart and TimeEnd, RoomId...
Now I need to verify that a user is not able to select a room twice in a date/timerange. does anybody know hów to do that efficiently? 😕

I would be very glad about a quick answer or a little example 🙂

bubblenut

Just wondering why you have different fields for DateStart and TimeStart? If they are date and time fields in the database it should be very easy, something like.

SELECT COUNT(*) FROM theTable WHERE DateStart<'$myTimeISO8601' AND TimeStart<'$timePart' AND DateEnd>'$myTimeISO8601' AND TimeEnd>'$timePart';

You can fetch the ISO8601 formatted date and build the time part using php's [man]date[/man] function.

It would be a good idea to put indexes on the DateStart, DateEnd, TimeStart and TimeEnd fields (although I would suggest just using a TimeStart and TimeEnd which includes both the date and time). I can't really give you any more hints on the DB side without knowing what database you're using.

leatherback

Hi Bubble,

Not sure. thin kthis is NOT going to work:

date: august 1st, 10 PM to august 2nd 10PM blocked.
What would your example do for August 1st 5 AM?

Ice: You SHOULD use one colume, datetime, for start, and one for end. Makes this sort of queries much easier to manage. Please explain the logic between a total of 4 colums instead of 2, or convert them to two colums.

Then use the example bubble gave you to formulate your query. I'd use unix timestamps for queries

bubblenut

I'm assuming the time has been formatted accordingly. If the time has been stored as a time field you will be able to compare it against the ISO 8601 time part, if someone tries to compare time values numerically with 12 hr formatted times then they're a little bit beyond help really :p

[edit] If you're interested, check out the ISO 8601 page
[/edit]

leatherback

bubblenut wrote:
if someone tries to compare time values numerically with 12 hr formatted times then they're a little bit beyond help really :p

Hm.. You have been on the board long enough. Nothing surprises me anymore.. Couldn't openm the page you gave.

It is just.. Date and time to not match, and do not know about eachother. But perhaps the function you uses fixes it. I do not know it! : :p

bubblenut

Yea, I guess, so. Fair point.

Date wouldn't do it for you completely, you'd have to tell date how to format it but that could be just show the date in 24hr or show the date or print out the complete ISO 8601.