Categories
datetime language-agnostic math

Determine Whether Two Date Ranges Overlap

1498

Given two date ranges, what is the simplest or most efficient way to determine whether the two date ranges overlap?

As an example, suppose we have ranges denoted by DateTime variables StartDate1 to EndDate1 and StartDate2 to EndDate2.

9

  • 3

    Extremely similar to stackoverflow.com/questions/306316/…

    Nov 28, 2008 at 14:54

  • 1

    @CharlesBretana thanks for that, you’re right – that’s almost like a two-dimensional version of my question!

    Nov 28, 2008 at 14:57

  • 2

    very similar to stackoverflow.com/questions/117962/…

    Nov 28, 2008 at 16:35

  • 2

    Divide the situation ‘the two date ranges intersect’ into cases (there are two) then test for each case.

    Oct 12, 2012 at 23:14

  • 1

    Hi.. A: StartDate1, B: EndDate1, C: StartDate2, D: EndDate2. if B < C or A > D then we assume that they are not intersected.. So, we can easily test with ” isintersects = not (B < C or A > D) ” this will give us always whether it intersects or not.

    Feb 11, 2020 at 12:20


2750

(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)

NOTE4. Thanks to Carl for noticing this, but another answer shows an equivalent mathematical expression for this logical expression. Because the product of any two real numbers with opposite sign is negative and with the same sign it is positive, if you convert the datetimes to fractional numbers (and most DBMSs internally use numbers to represent datetimes), the above logical expression can also be evaluated using the following mathematical expression:

(EndA  - StartA) * (StartB - EndB) <= 0

15

  • 52

    This is a simplified logic based on these two assumptions: 1) StartA < EndA; 2) StartB < EndB. It seems to be obvious but in reality data can come from unknown source like user input or a database without sanitization. Keep in mind that you will need to validate input data to make sure those two assumptions are true before you can use this simplified logic or everything will be falling apart. Lesson learned from my own experience 😉

    – Devy

    Jul 27, 2015 at 19:08


  • 14

    @Devy, You are correct. Except that it will also work if startA = endA. Indeed, that’s exactly what the words Start and End mean. If you have two variables named Top and Bottom, or East and West, or HighValue and LoValue, it can be assumed or implied that something or someone, somewhere should be ensuring that one of the pairs of values are not stored in the opposite variables. -Only one of the two pairs because, well, it will also work if both pairs of values are switched.

    Aug 5, 2015 at 12:08


  • 3

    @rashid, here’s a post the might give you some hints on how to get the actual amount of overlap.

    – Baodad

    Dec 15, 2015 at 17:39

  • 24

    You can easily add nullable start and end (with the semantic that “null start” = “From the beginning of the time” and “null end” = “To the end of the time”) like that: (startA === null || endB === null || startA <= endB) && (endA === null || startB === null || endA >= startB)

    Feb 26, 2016 at 13:38


  • 3

    This is the best answer I ever saw in the StackOverflow.

    – Sampath

    Oct 17, 2017 at 7:27

499

I believe that it is sufficient to say that the two ranges overlap if:

(StartDate1 <= EndDate2) and (StartDate2 <= EndDate1)

6

  • 98

    I find the (StartDate1 <= EndDate2) and (EndDate1 >= StartDate2) notation easier to understand, Range1 is always on left in the tests.

    – A.L

    Dec 2, 2013 at 14:46

  • 14

    This assumes start and end dates are inclusive. Change <= to < if start is inclusive and end is exclusive.

    Apr 9, 2014 at 2:54

  • This will work very well even if startDate2 is before startDate1. So no need to assume that startDate1 is earlier than startDate2.

    Feb 6, 2015 at 5:04


  • 3

    I found (StartDate1 <= EndDate2) and (StartDate2 <= EndDate1) notation (as per answer) easier to understand than that in other answers.

    – apc

    Aug 4, 2016 at 8:44


  • How to adapt so that it works with data that has StartDate1 AND/OR EndDate1? The code assumes that StartDate1 and EndDate1 are always present. What if StartDate1 is given but no EndDate1 OR EndDate1 given but not StartDate1. How to handle this extra case?

    – juFo

    Aug 6, 2019 at 15:18


161

This article Time Period Library for .NET describes the relation of two time periods by the enumeration PeriodRelation:

// ------------------------------------------------------------------------
public enum PeriodRelation
{
    After,
    StartTouching,
    StartInside,
    InsideStartTouching,
    EnclosingStartTouching,
    Enclosing,
    EnclosingEndTouching,
    ExactMatch,
    Inside,
    InsideEndTouching,
    EndInside,
    EndTouching,
    Before,
} // enum PeriodRelation

enter image description here

2