CODEWITHSUNDEEP
mysqldateoverlap
stdcerr
stdcerr

Reputation: 15598

Prevent creation of overlapping date ranges

I have a certain event in the calendar assigned to a storeID that has a sdate (ShowDate) and an hdate (HideDate). Now for every new event with this storeID, I need to make sure that there's no overlap with an existing date period. Does that make sense? How do I do that? I read about stuff but couldn't figure out how to "insert only if not overlap".

Upvotes: 3

Views: 2577

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753525

In general, if you have two ranges with S1..E1 and S2..E2 as the start and end values of the ranges, then you have an overlap if:

  • S1 < E2 and
  • S2 < E1

This is symmetric, which is good (and reassuring). You will need to decide carefully whether those 'less than' operations should be 'less than or equal'; both can make sense, depending on how you store your data (open vs closed vs half-open or open-closed and closed-open ranges, etc). You can see a diagram of the possibilities on the question 'Determine whether two date ranges overlap'.

In your context, the 'ShowDate' corresponds to the start date, and the 'HideDate' corresponds to the end date.

It sounds rather as if you want to do a 'conditional insert', too. In this case, assuming that your StoreID is 1001, the ShowDate is 2012-03-21 and the HideDate is 2012-03-28, then you might write:

INSERT INTO Calendar(StoreID, ShowDate, HideDate, ...)
   SELECT 1001, '2012-03-21', '2012-03-28', ...
     FROM Dual
    WHERE NOT EXISTS(SELECT * FROM Calendar C2
                      WHERE C2.StoreID   = 1001
                        AND C2.ShowDate  < '2012-03-28'
                        AND '2012-03-21' < C2.HideDate
                    );

The values in the SELECT-list are those you want to add to the Calendar table. The sub-select means you get either 0 rows (because there is an overlap) or 1 row (because there is no overlap) with the data for your new value.

Upvotes: 6

Related Questions