MySQL - Insert Date Range into Date Columns If Dates Don't Overlap with Existing Ones

UPDATE date range into date columns if dates don't overlap with existing ones

You can use not exists in both insert and update queries:

insert into a_table (name, starttime, endtime) 
select 'test', '2016-10-12 22:00:00', '2016-10-12 23:00:00'
where not exists (
    select 1 from a_table
    where starttime < '2016-10-12 23:00:00' and endtime > '2016-10-12 22:00:00'
    );

update a_table 
set name = 'test2', starttime = '2016-10-12 22:15:00', endtime = '2016-10-12 23:00:00'  
where id = 1 
and not exists (
    select 1 from a_table
    where id <> 1 
    and starttime < '2016-10-12 23:00:00' and endtime > '2016-10-12 22:15:00'
    );

MySQL overlapping dates, none conflicting

I'm using PHP/MySQL and am going on the basis that a query can be run and if there 'are' matching results then, fail, if there 'arent' matching results then insert.

Well, try this. Here :date: is the date of the entry you are going to add, and :start-time: and :finish-time: are its start and finish times respectively.

SELECT EXISTS (
    SELECT
        1
    FROM
        TableName
    WHERE
        `date` = :date: AND
        ( :start-time: BETWEEN startTime AND finishTime OR
          :finish-time: BETWEEN startTime AND finishTime OR
          startTime BETWEEN :start-time: AND :finish-time:
          )
) AS `Clash`

Find overlapping of dates: How to find the dates of the user overlaps with other users in mysql?

Using exists logic along with the formula for overlapping date ranges we can try:

SELECT User,
       CASE WHEN EXISTS (SELECT 1 FROM dates_range dr2
                         WHERE dr2.start_date < dr1.end_date AND
                               dr2.end_date > dr1.start_date AND
                               dr2.User <> dr1.User)
            THEN 'Overlap' ELSE 'No_Overlap' END AS status
FROM dates_range dr1
ORDER BY User;

How to store date and time ranges without overlap in MySQL

It's pretty easy to determine if a given period doesn't overlap with another period.

For ease of expressing the comparison, for period 1, we'll let the begin and end be represented by b1 and e1. For period 2, b2 and e2.

There is no overlap if the following is true:

  b1 > e2 OR e1 < b2

(We can quibble whether equality of b1 and e2 would be considered an overlap or not, and adjust as necessary.)

The negation of that test would return TRUE if there was an overlap...

 NOT (b1 > e2 OR e1 < b2)

So, to find out if there is a row that overlaps with the proposed period, we would need a query that tests whether a row is returned...

Let's assume that table we are going to check has columns st and et (DATETIME) representing the beginning and ending of each period.

To find rows with an overlap with a proposed period bounded by b1 and e1

  SELECT t.* FROM t WHERE NOT (b1 > t.et OR e1 < t.st)

So for a query to just check for the existence of an overlapping row, we could do something like this:

  SELECT EXISTS (SELECT 1 FROM t WHERE NOT (b1 > t.et OR e1 < t.st))

That's pretty simple.

---

It's going to look a lot more complicated when we make the adjustment for the (inexplicable) split of the date and time components of a datetime into separate columns (as shown in the question).

It's just a straightforward matter of combining the separate date and time values together into a single value of DATETIME datatype.

All we need to do is substitute into our query above an appropriate conversion, e.g.

  st =>  CONCAT(pickup_date,' ',pickup_time) + INTERVAL 0 DAY
  et =>  CONCAT(return_date,' ',return_time) + INTERVAL 0 DAY

Same for b1 and e1.

Doing that substitution, coming up with the final query, is left as an exercise for whoever decided that storing date and time as separate columns was a good idea.

Make sure range value doesn't overlap with existing ranges

I'd create an array with all the values in the [min,max] range, then do an intersect with all the existing ranges:

$new_range = range($min, $max);
foreach ($current_ranges as $range) {
  if (count(array_intersect($new_range, range($range["min"], $range["max"])))) {
    throw new RangeException();
  }
}

This will throw an exception on the first already-existing range coinciding with the new one.

Determine Whether Two Date Ranges Overlap

(StartA <= EndB) and (EndA >= StartB)

Proof:

Let ConditionA Mean that DateRange A Completely After DateRange B

_                        |---- DateRange A ------|
|---Date Range B -----|                          _

(True if StartA > EndB)

Let ConditionB Mean that DateRange A is Completely Before DateRange B

|---- DateRange A -----|                        _ 
_                          |---Date Range B ----|

(True if EndA < StartB)

Then Overlap exists if Neither A Nor B is true -

(If one range is neither completely after the other,

nor completely before the other,
then they must overlap.)

Now one of De Morgan's laws says that:

Not (A Or B) <=> Not A And Not B

Which translates to: (StartA <= EndB) and (EndA >= StartB)

---

NOTE: This includes conditions where the edges overlap exactly. If you wish to exclude that,

change the >= operators to >, and <= to <

---

NOTE2. Thanks to @Baodad, see this blog, the actual overlap is least of:

{ endA-startA, endA - startB, endB-startA, endB - startB }

(StartA <= EndB) and (EndA >= StartB)
(StartA <= EndB) and (StartB <= EndA)

---

NOTE3. Thanks to @tomosius, a shorter version reads:

DateRangesOverlap = max(start1, start2) < min(end1, end2)

This is actually a syntactical shortcut for what is a longer implementation, which includes extra checks to verify that the start dates are on or before the endDates. Deriving this from above:

If start and end dates can be out of order, i.e., if it is possible that startA > endA or startB > endB, then you also have to check that they are in order, so that means you have to add two additional validity rules:

(StartA <= EndB) and (StartB <= EndA) and (StartA <= EndA) and (StartB <= EndB)
or:

(StartA <= EndB) and (StartA <= EndA) and (StartB <= EndA) and (StartB <= EndB)
or,

(StartA <= Min(EndA, EndB) and (StartB <= Min(EndA, EndB))
or:

(Max(StartA, StartB) <= Min(EndA, EndB)

But to implement Min() and Max(), you have to code, (using C ternary for terseness),:

(StartA > StartB? Start A: StartB) <= (EndA < EndB? EndA: EndB)

Summing range of dates without counting overlaps in mysql

This is hectic as anything but should get you what you need:

SELECT SUM(PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM a.end_date), EXTRACT(YEAR_MONTH FROM a.start_date))) months
  FROM (
    SELECT MIN(g.start_date) start_date, MAX(g.end_date) end_date 
      FROM (
        SELECT @group_id := @group_id + (@end_date IS NULL OR o.start_date > @end_date) group_id,
               start_date,
               @end_date := DATE(CASE 
                 WHEN (@end_date IS NULL OR o.start_date > @end_date) THEN o.end_date
                 ELSE GREATEST(o.end_date, @end_date)
               END) end_date  
          FROM overlap o
          JOIN (SELECT @group_id := 0, @end_date := NULL) init
      ORDER BY o.start_date ASC  
            ) g
  GROUP BY  g.group_id  
        ) a

The inner-most query groups together your periods in overlapping groups stretching the end_date where appropriate. The end_date flexes as I assumed that there could be periods completely enclosed by the previous.

The next wrapping query extracts the full range from each group.

The outer query sums up the full month diffs for each group. All group diffs are rounded down to the nearest full month by PERIOD_DIFF.

Unfortunately I couldn't test this as SQLFiddle has died on me.

---

Related Topics