2

I think this should be simpler than my head is treating it - but I've been staring at it for too long...

I have a database driven competitions engine for a client. They can log in and upload a prize photo, question, start / end dates etc.

All works great - except they're now changing the system to allow only one competition to be running at a time. So when someone tries to add a new one I need to validate it doesn't overlap with any other competitions already in the database...

My head has managed to get me this far :

$db->query('SELECT
    1 
FROM
    ' . DB_T_PREFIX . 'competition
WHERE
    (
        start_date <= "'.$fldStartDate->getValue().'"
    AND
        close_date >= "'.$fldStartDate->getValue().'"
    )
AND
    deleted = "0000-00-00 00:00:00"');

Can someone sanity check me that this should cover any overlaps? I'm pretty sure I need more conditions to cover this but head has turned to mush... hate working with date ranges.

Thanks,

Steve

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
steve
  • 2,469
  • 1
  • 23
  • 30
  • 2
    Somebody already answered this question before: http://stackoverflow.com/questions/2545947/mysql-range-date-overlap-check – Alexander Sulfrian Sep 20 '11 at 11:08
  • Thanks - not sure that's 100% what I need though. Potentially my admin users could add these competitions in any order, with a new one created in a gap between existing entries in the db... – steve Sep 20 '11 at 11:16
  • In the linked answer there are all possible aligned dates listed and proofed, that it always work. But notice, that it is not the same solution you have. – Alexander Sulfrian Sep 20 '11 at 11:18

2 Answers2

0

I wrote a scheduling application once that did something like that. I used the query below:

$query = "SELECT ... WHERE ((a.start_time < '$start' AND a.end_time > '$start') OR (a.start_time > '$start' AND a.end_time < '$end')) AND i.user_id=$userId";

$start is the new event's start time, $end is the end time for the new event. Also make sure that $start and $end are formatted as 'Y-m-d H:i:s'

Peter
  • 6,509
  • 4
  • 30
  • 34
0

Painting time related things is often helpfull:

Timeline ( |------| is one competition; |.......| is "free" time)

Schedule:
...|-----------|.........|-------------------|......|--------|....>
         X                         Y                    Z
You want to create a new Competition:
...................|-------------|................................>
                         NEW

As you can see this timeframe intersects with the Y competition. In order to find each intersecting competition check this in DB (pseudocode):

SELECT 1 FROM x WHERE
  start_date < new_start_date AND end_date > new start_date //intersect left
  OR
  start_date < new_end_date   AND end_date > new_end_date //intersect right
DerShodan
  • 463
  • 7
  • 16