Timestamp ranges

[adummer]

Hello,

I'm working on a scheduling application and want to validate new entries so things don't overlap.

There is a table called Activities which has 2 important columns, TimeStamp (UNIX timestamp) and Duration.

Lets say there are these rows:
TimeStamp = 1000, Duration = 500
TimeStamp = 2000, Duration = 250

So this means the time from 1000 to 1500, and the time from 2000 to 2250 is reserved.

Now if someone wants to start an activity at 1200, it will tell you that spot is taken.

If they start one at 1500 and it overlaps onto another activity it will tell you also.

I am using PHP4 and MySQL 3.23 and have tried the BETWEEN statement to no luck. Does anyone have experience with this type of problem?

[[deleted]]

What you need is a query that will check if there are any records where the start of the new activity is between the start and end of any old activity.

Something like

SELECT *
FROM table
WHERE new_start > start
AND new_start < (start + duration)

if such a record exists, the new activity will overlap.

[adummer]

Yes, your SQL works to see if the start overlaps on any old activity. But what about if the new activity is so long that it overlaps onto the start of an activity later in the day? That's the part I can't get past.

[[deleted]]

Then just add the new item's durection into the query:

SELECT *
FROM table
WHERE
(new_start > start
AND
new_start < (start + duration))
OR
(new_start + new_duration > start
AND
new_start + new_duration < (start + duration))

Basically you just check to see if the new activity starts or ends during an existing activity.

Now all that remains is to see if there are any old activities that start AND end durcint the new activity (when the new activity begins before, and ends after the existing activity)

[adummer]

vincent, thanks for the help. The query you gave in your last post almost works. It works for everything except one condition:

Lets say there is a meeting from 9am to 11am. If I enter a meeting from 8am to 10am, the verification says it is ok. Why is this?

[[deleted]]

Probably a bug somewhere in the code.
The query should notice that 10AM is between 9 and 11, and it should return that record.

Codesample?

[Anon]

Here is the query that I use:

SELECT COUNT(*) AS Count FROM Activities WHERE UserID=\'$UserID\' AND ( ($Start > TimeStamp AND $Start < (TimeStamp + Duration)) OR ($Start + $Duration > TimeStamp AND $Start + $Duration < (TimeStamp + Duration)) )

If it returns > 0 I give the user an error message. It works for everything except the condition I explained in the last post.

[[deleted]]

That looks correct.
Are you sure that the data in the tabel is correct?

[Anon]

Yes, I am sure the data is correct. It is entered via the program, not humans, so it IS being entered correctly :-) Anyway, I did manually verify it also. This one is stumping me!

[[deleted]]

Any change that you could dump some of the offending records so I can have a peek?