condition to not allow duplicate dates

wfastenow

Hello,

I have a table that runs a calendar application. There is a colomn for start datetime and a colomn for end datetime of each event.
I'm trying to figure out how I can write a condition (perhaps for a unique key?) that will not allow a new event to be posted if a previously entered event has already posted in that time frame.

I was thinking something like:

{datestart_eve} <= {rsEvents.dateend_eve} && {datestart_eve} <= {rsEvents.datestart_eve}

but it's note working. Any ideas would be hugely appreciated.

Thanks,
Willie

Sxooter

Try using between:

where newtime not between startcol and endcol

wfastenow

Hi Sxooter,

Thanks for the idea. I think between will work well.

I apologize, I have very spotted knowledge of PHP... can you tell me how I can check the condition before a user submits the form?

Thanks so much for your help.

Willie

Sxooter

Just do a select with the between and see if you get any rows back.

Note that this is open to a race condition, so you might wanna chech the data right after insert to make sure no one else has inserted at about the same time.

Me personally, I'd use a check condition and / or a unique functional index in PostgreSQL to do this, which means all I'd do is attempt the insert and check for errors instead of jumping through hoops and trying to avoid race conditions.

wfastenow

Hi again,

I've been trying:

SELECT * FROM `event_eve` WHERE `datestart_eve` BETWEEN `datestart_eve` AND `dateend_eve`

and

SELECT * FROM `event_eve` WHERE `datestart_eve` <= `dateend_eve` && `datestart_eve` >= `datestart_eve`

Both seem to act about the same. And neither is selecting from the same row. Thus, every row is returned.

I've also been trying to put some sort of check unique key trigger in my php code, but I can't get anything to recognize a single row as an event (and therefore an 'occupied' time), rather than take the latest enddate and the earliest startdate and have the entirety of the range 'occupied.'

I know I'm just missing something silly. There has got to be something I can do to do a conditional check.

Thanks for your time,
Willie

Sxooter

Your between needs to be of the form:

where '2005-05-01 12:00:00' between date_start and date_end

where date_start and date_end are columns in your table.

Your current where clause is comparing the dates already in the table to itself, which won't do much.