Determine Whether Two Date Ranges Overlap

Question

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.

Souza

Extremely similar to #306816 – Jellied

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

very similar to #118462 – Ensilage

Divide the situation 'the two date ranges intersect' into cases (there are two) then test for each case. – Kahle

I know this has been tagged as language-agnostic, but for all of you implementing in Java: Don't reinvent the wheel and use Joda Time. joda-time.sourceforge.net/api-release/org/joda/time/base/… – Diller

If dates can be NULL values (or empty), when they are not set, there is this question which is an extension of this one. – Felodese

Merge Overlapping Intervals algorithm can give some leads. – Reify

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. – Oligoclase

Yet another interval utility for .Net github.com/AlexeyBoiko/IntervalUtility (I am the author) – Smalt

Answer 1

(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) ? StartA : StartB) <= ((EndA < EndB) ? EndA : EndB)

Jellied

anyway we can get the actual number of overlap seconds – Puffin

Yes, but I don't think there's a simple way... Without writing actual SQL one algorithm would be: using A0, A1, B0 and B1 as points: When No Overlap, then 0; When Bo and B1 are both between A0 and A1, then it's B1-B0; When only B0 is between A0 and A1, then it's A1-B0; when only B1 is between A0 and A1 then it's B1-A0; else, it's A1-A0 – Jellied

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 ;) – Mundt

@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. – Jellied

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

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) – Buggery

silentmatt.com/rectangle-intersection Use this tool to check different cases easily with drag and drop and verify your solution. – Growing

@KevinRobatel, This works for all scenarios but when both endA and endB are null, this is a scenario where no matter which dates are set, they will conflict, but in your scenario they pass. – Franz

How about inverted logic? Something like: NOT (EndA < StartB OR StartA > EndB) – Isotonic

@Giuse, Read my answer. I start out with that. In the middle, I apply de Morgan's law to convert it to the shorter but equivalent representation. – Jellied

My solution: database_record_start_date <= filter_end_date and database_record_end_date >= filter_start_date Example: database_record_start_date <= '2022-02-28' and database_record_end_date >= '2022-02-01' – Tait

This seems to be the best approach. However I found an alternate, interesting way of doing it here: https://mcmap.net/q/33044/-find-if-given-date-range-inside-another-date-range-in-mysql – Curvilinear

@Carl, your alternate approach is actually just the equivalent mathematical expression for the logical expression above. When two real numbers have the opposite sign, their product is negative, when they have the same sign, the product is positive. – Jellied

Answer 2

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

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

Souza

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

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

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

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

How can I make start exclusive & end inclusive? – Bennettbenni

Answer 3

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

Andrien

Nice, I have implemented Allens interval algebra in Java, too, see the API of IntervalRelation and IsoInterval – Luigiluigino

Answer 4

For reasoning about temporal relations (or any other interval relations, come to that), consider Allen's Interval Algebra. It describes the 13 possible relations that two intervals can have with respect to each other. You can find other references — "Allen Interval" seems to be an operative search term. You can also find information about these operations in Snodgrass's Developing Time-Oriented Applications in SQL (PDF available online at URL), and in Date, Darwen and Lorentzos Temporal Data and the Relational Model (2002) or Time and Relational Theory: Temporal Databases in the Relational Model and SQL (2014; effectively the second edition of TD&RM).

---

The short(ish) answer is: given two date intervals A and B with components .start and .end and the constraint .start <= .end, then two intervals overlap if:

A.end >= B.start AND A.start <= B.end

You can tune the use of >= vs > and <= vs < to meet your requirements for degree of overlap.

---

ErikE comments:

You can only get 13 if you count things funny... I can get "15 possible relations that two intervals can have" when I go crazy with it. By sensible counting, I get only six, and if you throw out caring whether A or B comes first, I get only three (no intersect, partially intersect, one wholly within other). 15 goes like this: [before:before, start, within, end, after], [start:start, within, end, after], [within:within, end, after], [end:end, after], [after:after].

I think that you cannot count the two entries 'before:before' and 'after:after'. I could see 7 entries if you equate some relations with their inverses (see the diagram in the referenced Wikipedia URL; it has 7 entries, 6 of which have a different inverse, with equals not having a distinct inverse). And whether three is sensible depends on your requirements.

----------------------|-------A-------|----------------------
    |----B1----|
           |----B2----|
               |----B3----|
               |----------B4----------|
               |----------------B5----------------|
                      |----B6----|
----------------------|-------A-------|----------------------
                      |------B7-------|
                      |----------B8-----------|
                         |----B9----|
                         |----B10-----|
                         |--------B11--------|
                                      |----B12----|
                                         |----B13----|
----------------------|-------A-------|----------------------

Ranaerancagua

You can only get 13 if you count things funny... I can get "15 possible relations that two intervals can have" when I go crazy with it. By sensible counting, I get only six, and if you throw out caring whether A or B comes first, I get only three (no intersect, partially intersect, one wholly within other). 15 goes like this: [before:before, start, within, end, after], [start:start, within, end, after],[within:within, end, after], [end:end, after], [after:after]. – Fur

@Emtucifor: I think that you cannot count the two entries 'before:before' and 'after:after'. – Ranaerancagua

Re your update: B1 to A is before:before and B13 to A is after:after. Your nice diagram is missing start:start between B5 B6, and end:end between B11 and B12. If being on an endpoint is significant, then you have to count it, so the final tally is 15, not 13. I don't think the endpoint thing is significant, so I personally count it [before: before, within, after], [within: within, after], [after:after] which comes to 6. I think the whole endpoint thing is just confusion about whether the bounds are inclusive or exclusive. The exclusiveness of the endpoints don't change the core relations! – Fur

That is, in my scheme the these are equivalent: (B2, B3, B4), (B6, B7, B9, B10), (B8, B11, B12). I realize that B7 implies the information that the two ranges exactly coincide. But I'm not convinced this additional information should be part of the base intersection relations. For example, when two intervals are the exact same length even if not coincident or even overlapping, should that be considered another "relation"? I say no, and seeing as this additional aspect is the only thing making B7 distinct from B6, then I think having endpoints-as-separate-cases makes things inconsistent. – Fur

@Emtucifor: OK - I see why I misidentified 'before:before' and 'after:after' as the entries; however, I cannot picture what the 'start:start' and 'end:end' entries should look like. Since you can't edit my diagram, can you email me (see my profile) with a modified copy of the diagram showing the 'start:start' and 'end:end' relationships? I have no major issues with your groupings. – Ranaerancagua

start:start would be starting and ending at the same time, the beginning of A. Since time in computers always has a granularity or resolution, this still denotes a (possibly very short) time range. end:end would be starting and ending at the end of A. I'll do the email for completeness. – Fur

After an email exchange with Jonathan, I realized that in my model where the start time is inclusive and the end time is exclusive, a range beginning and stopping at the same time is zero length (because it ends before it starts). So [start:start] and [end:end] don't make sense in my scheme. However, the scheme Jonathan suggests requires that the endpoints are inclusive, otherwise ranges B2 and A (for example) would not intersect. This to me is problematic because times in a computer are not represented to infinite precision. See next comment -> – Fur

If the end time is inclusive, then to denote non-overlapping but contiguous ranges in a computer, one has to subtract the smallest time increment allowed from the end time of the earlier segment. In SQL Server with the datetime data type, for example, the ranges A:'20100101 09:00' - '20100101 10:00' and B:'20100101 10:00' - '20100101 11:00' would overlap by 1/300th of a millisecond. To make them contiguous and non-overlapping, the first range would have to be adjusted to A:'20100101 09:00' - '20100101 09:59:59.997'. But this is completely unacceptable for multiple reasons (Next ->) – Fur

The first and less important reason is that people don't think of times this way. They want to say that the range begins at 9 and ends at 10. If you're collecting data from a user then you either have to present the 09:59:59.997 garbage (this would never work) or adjust all the times when you store them (not much better). The second and more important reason is that the underlying data type shouldn't change the business meaning of data. What iff the datetime column was converted to datetime2 (technet.microsoft.com/en-us/library/bb677335.aspx) which has 00:00:00 through 23:59:59.9999999? – Fur

Now there is a gap between the end of A and the start of B. The only sensible solution to all this madness is to just to treat end times as exclusive. Once this is done, the 13 ranges mentioned by Jonathan completely break down. There is no special difference between two non-overlapping ranges that are adjacent or separated. After removing collapsed or equivalent cases, we are left with only 6 basic relations (3 if A and B are considered interchangeably). – Fur

Answer 5

If the overlap itself should be calculated as well, you can use the following formula:

overlap = max(0, min(EndDate1, EndDate2) - max(StartDate1, StartDate2))
if (overlap > 0) { 
    ...
}

Fledgling

so overlap is the amount of time that the two events share? Does this work for all the different ways events can overlap? – Grisons

This does not work when there is complete overlap. For e.g. Range1: 1-7 Range2: 4-5 – Reavis

Answer 6

All the solutions that check a multitude of conditions based on where the ranges are in relation to one another can be greatly simplified by simply ensuring that one range starts before or at the same time as the other. You can do this by swapping the ranges if necessary up front.

Then, you can detect overlap if the second range start is:

• less than or equal to the first range end (if ranges are inclusive, containing both the start and end times); or
• less than (if ranges are inclusive of start and exclusive of end).

For example (assuming inclusive at both ends), there's only four possibilities for range 2, of which one is a non-overlap (the > at the end of the range means it doesn't matter where the range ends):

|-----|        range 1, lines below are all range 2.
|-->  :        overlap.
 |--> :        overlap.
      |--->    overlap (no overlap in exclusive-of-end case).
       |--->   no overlap.

The endpoint of the second range doesn't affect the result at all. So, in pseudo-code, you can do something like (assuming s <= e holds for all ranges - if not, you may have top swap them as well):

def overlaps(r1, r2):
    if r1.s > r2.s:
        swap r1, r2
    return r2.s <= r1.e

Or, the one-level-limit recursive option:

def overlaps(r1, r2):
    if r1.s <= r2.s:
        return r2.s <= r1.e
    return overlaps(r2, r1)

If the ranges are exclusive at the end, you just have to replace <= with < in the expression you return (in both code snippets).

This greatly limits the number of checks you have to make because you remove half of the problem space early by ensuring the first range never starts after the second.

---

And, since "code talks", here is some Python code that shows this in action, with quite a few test cases. First, the InclusiveRange class:

class InclusiveRange:
    """InclusiveRange class to represent a lower and upper bound."""

    def __init__(self, start, end):
        """Initialisation, ensures start <= end.
        Args:
            start: The start of the range.
            end: The end of the range.
        """
        self.start = min(start, end)
        self.end = max(start, end)

    def __repr__(self):
        """Return representation for f-string."""
        return f"({self.start}, {self.end})"

    def overlaps(self, other):
        """True if range overlaps with another.
        Args:
            other: The other InclusiveRange to check against.
        """

        # Very limited recursion to ensure start of first range
        # isn't after start of second.

        if self.start > other.start:
            return other.overlaps(self)

        # Greatly simplified check for overlap.

        return other.start <= self.end

Then a test case handler to allow us to nicely present the result of a single test case:

def test_case(range1, range2):
    """Single test case checker."""

    # Get low and high value for "graphic" output.

    low = min(range1.start, range2.start)
    high = max(range1.end, range2.end)

    # Output ranges and graphic.

    print(f"r1={range1} r2={range2}: ", end="")
    for val in range(low, high + 1):
        is_in_first = range1.start <= val <= range1.end
        is_in_second = range2.start <= val <= range2.end

        if is_in_first and is_in_second:
            print("|", end="")
        elif is_in_first:
            print("'", end="")
        elif is_in_second:
            print(",", end="")
        else:
            print(" ", end="")

    # Finally, output result of overlap check.

    print(f" - {range1.overlaps(range2)}\n")

Then finally, a decent chunk of test cases to which you can add your own if need be:

# Various test cases, add others if you doubt the correctness.

test_case(InclusiveRange(0, 1), InclusiveRange(8, 9))
test_case(InclusiveRange(0, 4), InclusiveRange(5, 9))
test_case(InclusiveRange(0, 4), InclusiveRange(4, 9))
test_case(InclusiveRange(0, 7), InclusiveRange(2, 9))
test_case(InclusiveRange(0, 4), InclusiveRange(0, 9))
test_case(InclusiveRange(0, 9), InclusiveRange(0, 9))
test_case(InclusiveRange(0, 9), InclusiveRange(4, 5))

test_case(InclusiveRange(8, 9), InclusiveRange(0, 1))
test_case(InclusiveRange(5, 9), InclusiveRange(0, 4))
test_case(InclusiveRange(4, 9), InclusiveRange(0, 4))
test_case(InclusiveRange(2, 9), InclusiveRange(0, 7))
test_case(InclusiveRange(0, 9), InclusiveRange(0, 4))
test_case(InclusiveRange(0, 9), InclusiveRange(0, 9))
test_case(InclusiveRange(4, 5), InclusiveRange(0, 9))

Running that produces the output:

r1=(0, 1) r2=(8, 9): ''      ,, - False
r1=(0, 4) r2=(5, 9): ''''',,,,, - False
r1=(0, 4) r2=(4, 9): ''''|,,,,, - True
r1=(0, 7) r2=(2, 9): ''||||||,, - True
r1=(0, 4) r2=(0, 9): |||||,,,,, - True
r1=(0, 9) r2=(0, 9): |||||||||| - True
r1=(0, 9) r2=(4, 5): ''''||'''' - True
r1=(8, 9) r2=(0, 1): ,,      '' - False
r1=(5, 9) r2=(0, 4): ,,,,,''''' - False
r1=(4, 9) r2=(0, 4): ,,,,|''''' - True
r1=(2, 9) r2=(0, 7): ,,||||||'' - True
r1=(0, 9) r2=(0, 4): |||||''''' - True
r1=(0, 9) r2=(0, 9): |||||||||| - True
r1=(4, 5) r2=(0, 9): ,,,,||,,,, - True

where each line has:

• the two ranges being evaluated;
• a graphical representation of the "range space" (from lowest start to highest end) where each character is a value in that "range space":
  • ' indicates a value in the first range only;
  • , indicates a value in the second range only;
  • | indicates a value in both ranges; and
  • indicates a value in neither range.
• the result of the overlap check.

You can see quite clearly that you only get true in the overlap check when there is at least one value in both ranges (i.e., a | character). Every other case gives false.

Feel free to use any other values if you want to add more test cases.

Alms

+1 for mentioning the inclusive/exclusive problem. I was going to whip up an answer myself when I had time, but no need now. The thing is you almost never allow both the start and end to be inclusive simultaneously. In my industry it is common practice to treat the start as exlusive and the end as inclusive, but either way is fine as long as you stay consistent. This is the first completely correct answer on this question so far...IMO. – Anthraquinone

Answer 7

Here is yet another solution using JavaScript. Specialities of my solution:

• Handles null values as infinity
• Assumes that the lower bound is inclusive and the upper bound exclusive.
• Comes with a bunch of tests

The tests are based on integers but since date objects in JavaScript are comparable you can just throw in two date objects as well. Or you could throw in the millisecond timestamp.

Code:

/**
 * Compares to comparable objects to find out whether they overlap.
 * It is assumed that the interval is in the format [from,to) (read: from is inclusive, to is exclusive).
 * A null value is interpreted as infinity
 */
function intervalsOverlap(from1, to1, from2, to2) {
    return (to2 === null || from1 < to2) && (to1 === null || to1 > from2);
}

Tests:

describe('', function() {
    function generateTest(firstRange, secondRange, expected) {
        it(JSON.stringify(firstRange) + ' and ' + JSON.stringify(secondRange), function() {
            expect(intervalsOverlap(firstRange[0], firstRange[1], secondRange[0], secondRange[1])).toBe(expected);
        });
    }

    describe('no overlap (touching ends)', function() {
        generateTest([10,20], [20,30], false);
        generateTest([20,30], [10,20], false);

        generateTest([10,20], [20,null], false);
        generateTest([20,null], [10,20], false);

        generateTest([null,20], [20,30], false);
        generateTest([20,30], [null,20], false);
    });

    describe('do overlap (one end overlaps)', function() {
        generateTest([10,20], [19,30], true);
        generateTest([19,30], [10,20], true);

        generateTest([10,20], [null,30], true);
        generateTest([10,20], [19,null], true);
        generateTest([null,30], [10,20], true);
        generateTest([19,null], [10,20], true);
    });

    describe('do overlap (one range included in other range)', function() {
        generateTest([10,40], [20,30], true);
        generateTest([20,30], [10,40], true);

        generateTest([10,40], [null,null], true);
        generateTest([null,null], [10,40], true);
    });

    describe('do overlap (both ranges equal)', function() {
        generateTest([10,20], [10,20], true);

        generateTest([null,20], [null,20], true);
        generateTest([10,null], [10,null], true);
        generateTest([null,null], [null,null], true);
    });
});

Result when run with karma&jasmine&PhantomJS:

PhantomJS 1.9.8 (Linux): Executed 20 of 20 SUCCESS (0.003 secs / 0.004 secs)

Achromat

Answer 8

Here is the code that does the magic:

 var isOverlapping =  ((A == null || D == null || A <= D) 
            && (C == null || B == null || C <= B)
            && (A == null || B == null || A <= B)
            && (C == null || D == null || C <= D));

Where..

• A -> 1Start
• B -> 1End
• C -> 2Start
• D -> 2End

Proof? Check out this test console code gist.

Cartilaginous

That works, but i would prefer to test for not overlapping, only two scenarios – Transpacific

Since, by definition A is always <= B and C is always <= D, you can simplify by (A <= D) && (C <= B) – Fromma

Answer 9

An easy way to remember the solution would be
min(ends)>max(starts)

Piccaninny

Answer 10

Here's my solution in Java, which works on unbounded intervals too

private Boolean overlap (Timestamp startA, Timestamp endA,
                         Timestamp startB, Timestamp endB)
{
    return (endB == null || startA == null || !startA.after(endB))
        && (endA == null || startB == null || !endA.before(startB));
}

Elvyn

I think you meant unbounded ends instead of open intervals. – Gelation

@Gelation both terms work en.wikipedia.org/wiki/Interval_(mathematics)#Terminology – Elvyn

!startA.after(endB) means startA <= endB and !endA.before(startB) means startB <= endA. These are the criterias for a closed interval and not an open interval. – Gelation

@Gelation true, and the other conditions such as endB == null and startA == null check for an open interval. – Elvyn

endB == null, startA == null, endA == null and startB == null are all criteria to check for an unbounded interval and not an open interval. Example for the differences between unbounded and open intervals: (10, 20) and (20, null) are two open intervals which do not overlap. The last one does have an unbounded end. Your function will return true, but the intervals do not overlap, because the intervals do not include 20. (used numbers instead of timestamps for simplicity) – Gelation

@Gelation I understand, I've fixed it now. – Elvyn

Answer 11

I would do

StartDate1.IsBetween(StartDate2, EndDate2) || EndDate1.IsBetween(StartDate2, EndDate2)

Where IsBetween is something like

    public static bool IsBetween(this DateTime value, DateTime left, DateTime right) {
        return (value > left && value < right) || (value < left && value > right);
    }

Gaylene

I would prefer (left < value && value < right) || (right < value && value < left) for this method. – Evangelineevangelism

Why would you check four conditions when you only have to check two? Fail. – Fur

Ah, my apologies, I see now that you are allowing the ranges to be in reverse order (StartDateX > EndDateX). Strange. Anyway, what if StartDate1 is less than StartDate2 and EndDate1 is greater than EndDate2? The code you gave will not detect this overlapping condition. – Fur

Won't this return false if Date1 contains whole Date2? Then StartDate1 is before StartDate2 and EndDate1 is after EndDate2 – Hydrolyte

Answer 12

The solution posted here did not work for all overlapping ranges...

----------------------|-------A-------|----------------------
    |----B1----|
           |----B2----|
               |----B3----|
               |----------B4----------|
               |----------------B5----------------|
                      |----B6----|
----------------------|-------A-------|----------------------
                      |------B7-------|
                      |----------B8-----------|
                         |----B9----|
                         |----B10-----|
                         |--------B11--------|
                                      |----B12----|
                                         |----B13----|
----------------------|-------A-------|----------------------

my working solution was:

AND (
  ('start_date' BETWEEN STARTDATE AND ENDDATE) -- caters for inner and end date outer
  OR
  ('end_date' BETWEEN STARTDATE AND ENDDATE) -- caters for inner and start date outer
  OR
  (STARTDATE BETWEEN 'start_date' AND 'end_date') -- only one needed for outer range where dates are inside.
) 

Demilune

Answer 13

As there have been several answers for different languages and environments, here is one for standard ANSI SQL.

In standard SQL it is as as simple as

(StartDate1, EndDate1) overlaps (StartDate2, EndDate2)

assuming all four columns are DATE or TIMESTAMP columns. It returns true if both ranges have at least one day in common (assuming DATE values)

(However not all DBMS products support that)

---

In PostgreSQL it's also easy to test for inclusion by using date ranges

daterange(StartDate1, EndDate1) @> daterange(StartDate2, EndDate2)

the above returns true if the second range is completely included in the first (which is different to "overlaps")

Zygophyte

Answer 14

This is an extension to the excellent answer by @charles-bretana.

The answer however does not make a distinction among open, closed, and half-open (or half-closed) intervals.

Case 1: A, B are closed intervals

A = [StartA, EndA]
B = [StartB, EndB]

                         [---- DateRange A ------]   (True if StartA > EndB)
[--- Date Range B -----]                           


[---- DateRange A -----]                             (True if EndA < StartB)
                         [--- Date Range B ----]

Overlap iff: (StartA <= EndB) and (EndA >= StartB)

Case 2: A, B are open intervals

A = (StartA, EndA)
B = (StartB, EndB)

                         (---- DateRange A ------)   (True if StartA >= EndB)
(--- Date Range B -----)                           

(---- DateRange A -----)                             (True if EndA <= StartB)
                         (--- Date Range B ----)

Overlap iff: (StartA < EndB) and (EndA > StartB)

Case 3: A, B right open

A = [StartA, EndA)
B = [StartB, EndB)

                         [---- DateRange A ------)   (True if StartA >= EndB) 
[--- Date Range B -----)                           

[---- DateRange A -----)                             (True if EndA <= StartB)
                         [--- Date Range B ----)

Overlap condition: (StartA < EndB) and (EndA > StartB)

Case 4: A, B left open

A = (StartA, EndA]
B = (StartB, EndB]

                         (---- DateRange A ------]   (True if StartA >= EndB)
(--- Date Range B -----]                           

(---- DateRange A -----]                             (True if EndA <= StartB)
                         (--- Date Range B ----]

Overlap condition: (StartA < EndB) and (EndA > StartB)

Case 5: A right open, B closed

A = [StartA, EndA)
B = [StartB, EndB]

                         [---- DateRange A ------)    (True if StartA > EndB)
[--- Date Range B -----]                           


[---- DateRange A -----)                              (True if EndA <= StartB)  
                         [--- Date Range B ----]

Overlap condition: (StartA <= EndB) and (EndA > StartB)

etc...

Finally, the general condition for two intervals to overlap is

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

where 🞐 turns a strict inequality into a non-strict one whenever the comparison is made between two included endpoint.

Grammarian

Cases two, three, and four have the same Overlap condition, is this intentional? – Henrietta

@Marie, I just listed a few cases (not all) – Grammarian

Answer 15

This was my JavaScript solution with moment.js:

// Current row dates
var dateStart = moment("2014-08-01", "YYYY-MM-DD");
var dateEnd = moment("2014-08-30", "YYYY-MM-DD");

// Check with dates above
var rangeUsedStart = moment("2014-08-02", "YYYY-MM-DD");
var rangeUsedEnd = moment("2014-08-015", "YYYY-MM-DD");

// Range covers other ?
if((dateStart <= rangeUsedStart) && (rangeUsedEnd <= dateEnd)) {
    return false;
}
// Range intersects with other start ?
if((dateStart <= rangeUsedStart) && (rangeUsedStart <= dateEnd)) {
    return false;
}
// Range intersects with other end ?
if((dateStart <= rangeUsedEnd) && (rangeUsedEnd <= dateEnd)) {
    return false;
}

// All good
return true;

Jubilation

Answer 16

Short answer using momentjs:

function isOverlapping(startDate1, endDate1, startDate2, endDate2){ 
    return moment(startDate1).isSameOrBefore(endDate2) && 
    moment(startDate2).isSameOrBefore(endDate1);
}

the answer is based on above answers, but it's shortened.

Advised

If the two date ranges are adjacent, this reports them as overlapping. (in other words, if dateRange1 ends at the time that dateRange2 begins, they don't overlap - but this function would say that they do.) – Pekingese

Answer 17

In Microsoft SQL SERVER - SQL Function

CREATE FUNCTION IsOverlapDates 
(
    @startDate1 as datetime,
    @endDate1 as datetime,
    @startDate2 as datetime,
    @endDate2 as datetime
)
RETURNS int
AS
BEGIN
DECLARE @Overlap as int
SET @Overlap = (SELECT CASE WHEN  (
        (@startDate1 BETWEEN @startDate2 AND @endDate2) -- caters for inner and end date outer
        OR
        (@endDate1 BETWEEN @startDate2 AND @endDate2) -- caters for inner and start date outer
        OR
        (@startDate2 BETWEEN @startDate1 AND @endDate1) -- only one needed for outer range where dates are inside.
        ) THEN 1 ELSE 0 END
    )
    RETURN @Overlap

END
GO

--Execution of the above code
DECLARE @startDate1 as datetime
DECLARE @endDate1 as datetime
DECLARE @startDate2 as datetime
DECLARE @endDate2 as datetime
DECLARE @Overlap as int
SET @startDate1 = '2014-06-01 01:00:00' 
SET @endDate1 =   '2014-06-01 02:00:00'
SET @startDate2 = '2014-06-01 01:00:00' 
SET @endDate2 =   '2014-06-01 01:30:00'

SET @Overlap = [dbo].[IsOverlapDates]  (@startDate1, @endDate1, @startDate2, @endDate2)

SELECT Overlap = @Overlap

Chainman

Answer 18

the simplest

The simplest way is to use a well-engineered dedicated library for date-time work.

someInterval.overlaps( anotherInterval )

java.time & ThreeTen-Extra

The best in the business is the java.time framework built into Java 8 and later. Add to that the ThreeTen-Extra project that supplements java.time with additional classes, specifically the Interval class we need here.

As for the language-agnostic tag on this Question, the source code for both projects is available for use in other languages (mind their licenses).

Interval

The org.threeten.extra.Interval class is handy, but requires date-time moments (java.time.Instant objects) rather than date-only values. So we proceed by using the first moment of the day in UTC to represent the date.

Instant start = Instant.parse( "2016-01-01T00:00:00Z" );
Instant stop = Instant.parse( "2016-02-01T00:00:00Z" );

Create an Interval to represent that span of time.

Interval interval_A = Interval.of( start , stop );

We can also define an Interval with a starting moment plus a Duration.

Instant start_B = Instant.parse( "2016-01-03T00:00:00Z" );
Interval interval_B = Interval.of( start_B , Duration.of( 3 , ChronoUnit.DAYS ) );

Comparing to test for overlaps is easy.

Boolean overlaps = interval_A.overlaps( interval_B );

You can compare an Interval against another Interval or Instant:

• abuts
• contains
• encloses
• equals
• isAfter
• isBefore
• overlaps

All of these use the Half-Open approach to defining a span of time where the beginning is inclusive and the ending is exclusive.

Diencephalon

Answer 19

I had a situation where we had dates instead of datetimes, and the dates could overlap only on start/end. Example below:

(Green is the current interval, blue blocks are valid intervals, red ones are overlapping intervals).

I adapted Ian Nelson's answer to the following solution:

   (startB <= startA && endB > startA)
|| (startB >= startA && startB < endA)

This matches all overlap cases but ignores the allowed overlap ones.

Arrival

Answer 20

The mathematical solution given by @Bretana is good but neglects two specific details:

1. aspect of closed or half-open intervals
2. empty intervals

---

About the closed or open state of interval boundaries, the solution of @Bretana valid for closed intervals

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

can be rewritten for half-open intervals to:

(StartA < EndB) and (EndA > StartB)

This correction is necessary because an open interval boundary does not belong to the value range of an interval by definition.

---

And about empty intervals, well, here the relationship shown above does NOT hold. Empty intervals which do not contain any valid value by definition must be handled as special case. I demonstrate it by my Java time library Time4J via this example:

MomentInterval a = MomentInterval.between(Instant.now(), Instant.now().plusSeconds(2));
MomentInterval b = a.collapse(); // make b an empty interval out of a

System.out.println(a); // [2017-04-10T05:28:11,909000000Z/2017-04-10T05:28:13,909000000Z)
System.out.println(b); // [2017-04-10T05:28:11,909000000Z/2017-04-10T05:28:11,909000000Z)

The leading square bracket "[" indicates a closed start while the last bracket ")" indicates an open end.

System.out.println(
      "startA < endB: " + a.getStartAsInstant().isBefore(b.getEndAsInstant())); // false
System.out.println(
      "endA > startB: " + a.getEndAsInstant().isAfter(b.getStartAsInstant())); // true

System.out.println("a overlaps b: " + a.intersects(b)); // a overlaps b: false

As shown above, empty intervals violate the overlap condition above (especially startA < endB), so Time4J (and other libraries, too) has to handle it as special edge case in order to guarantee that the overlap of any arbitrary interval with an empty interval does not exist. Of course, date intervals (which are closed by default in Time4J but can be half-open, too, like empty date intervals) are handled in a similar way.

Luigiluigino

Answer 21

In case you're using a date range that has not ended yet (still on going) e.g. not set endDate = '0000-00-00' you can not use BETWEEN because 0000-00-00 is not a valid date!

I used this solution:

(Startdate BETWEEN '".$startdate2."' AND '".$enddate2."')  //overlap: starts between start2/end2
OR (Startdate < '".$startdate2."' 
  AND (enddate = '0000-00-00' OR enddate >= '".$startdate2."')
) //overlap: starts before start2 and enddate not set 0000-00-00 (still on going) or if enddate is set but higher then startdate2

If startdate2 is higher then enddate there is no overlap!

Eolanda

Answer 22

The answer is too simple for me so I have created a more generic dynamic SQL statement which checks to see if a person has any overlapping dates.

SELECT DISTINCT T1.EmpID
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.EmpID = T2.EmpID 
    AND T1.JobID <> T2.JobID
    AND (
        (T1.DateFrom >= T2.DateFrom AND T1.dateFrom <= T2.DateTo) 
        OR (T1.DateTo >= T2.DateFrom AND T1.DateTo <= T2.DateTo)
        OR (T1.DateFrom < T2.DateFrom AND T1.DateTo IS NULL)
    )
    AND NOT (T1.DateFrom = T2.DateFrom)

Philipps

Answer 23

The easiest way to do it in my opinion would be to compare if either EndDate1 is before StartDate2 and EndDate2 is before StartDate1.

That of course if you are considering intervals where StartDate is always before EndDate.

Meandrous

Answer 24

Using Java util.Date, here's what I did:

public static boolean checkTimeOverlaps(Date startDate1, Date endDate1, Date startDate2, Date endDate2)
{
    if (startDate1 == null || endDate1 == null || startDate2 == null || endDate2 == null)
       return false;

    if ((startDate1.getTime() <= endDate2.getTime()) && (startDate2.getTime() <= endDate1.getTime()))
       return true;

    return false;
}

Overrule

Answer 25

For ruby I also found this:

class Interval < ActiveRecord::Base

  validates_presence_of :start_date, :end_date

  # Check if a given interval overlaps this interval    
  def overlaps?(other)
    (start_date - other.end_date) * (other.start_date - end_date) >= 0
  end

  # Return a scope for all interval overlapping the given interval, including the given interval itself
  named_scope :overlapping, lambda { |interval| {
    :conditions => ["id <> ? AND (DATEDIFF(start_date, ?) * DATEDIFF(?, end_date)) >= 0", interval.id, interval.end_date, interval.start_date]
  }}

end

Found it here with nice explaination -> http://makandracards.com/makandra/984-test-if-two-date-ranges-overlap-in-ruby-or-rails

Cavite

Answer 26

If you provide a date range as input and want to find out if it overlaps with the existing date range in database, the following conditions can successfully meet your demand

Assume you provide a @StartDate and @EndDate from your form input.

conditions are :

If @StartDate is ahead of existingStartDate and behind existingEndDate then we can say @StartDate is in the middle of a existing date range, thus we can conclude it will overlap

@StartDate >=existing.StartDate And @StartDate <= existing.EndDate) 

If @StartDate is behind existingStartDate but @EndDate is ahead of existingStartDate we can say that it will overlap

 (@StartDate <= existing.StartDate And @EndDate >= existing.StartDate)

If @StartDate is behind existingStartDate And @EndDate is ahead of existingEndDate we can conclude that the provided date range devours a existing date range , thus overlaps

 (@StartDate <= existing.StartDate And @EndDate >= existing.EndDate))

If any of the condition stands true, your provided date range overlaps with existing ones in the database.

Xantha

Answer 27

Split the problem into cases then handle each case.

The situation 'two date ranges intersect' is covered by two cases - the first date range starts within the second, or the second date range starts within the first.

Kahle

Answer 28

Using native PHP classes:

//custom date for example
$d1 = new DateTime("2012-07-08");
$d2 = new DateTime("2012-07-11");
$d3 = new DateTime("2012-07-08");
$d4 = new DateTime("2012-07-15");

//create a date period object
$interval = new DateInterval('P1D');
$daterange = iterator_to_array(new DatePeriod($d1, $interval, $d2));
$daterange1 = iterator_to_array(new DatePeriod($d3, $interval, $d4));
array_map(function($v) use ($daterange1) { if(in_array($v, $daterange1)) print "Bingo!";}, $daterange);

Skilled

Answer 29

public static class NumberExtensionMethods
    {
        public static Boolean IsBetween(this Int64 value, Int64 Min, Int64 Max)
        {
            if (value >= Min && value <= Max) return true;
            else return false;
        }

        public static Boolean IsBetween(this DateTime value, DateTime Min, DateTime Max)
        {
            Int64 numricValue = value.Ticks;
            Int64 numericStartDate = Min.Ticks;
            Int64 numericEndDate = Max.Ticks;

            if (numricValue.IsBetween(numericStartDate, numericEndDate) )
            {
                return true;
            }

            return false;
        }
    }

public static Boolean IsOverlap(DateTime startDate1, DateTime endDate1, DateTime startDate2, DateTime endDate2)
        {
            Int64 numericStartDate1 = startDate1.Ticks;
            Int64 numericEndDate1 = endDate1.Ticks;
            Int64 numericStartDate2 = startDate2.Ticks;
            Int64 numericEndDate2 = endDate2.Ticks;

            if (numericStartDate2.IsBetween(numericStartDate1, numericEndDate1) ||
                numericEndDate2.IsBetween(numericStartDate1, numericEndDate1) ||
                numericStartDate1.IsBetween(numericStartDate2, numericEndDate2) ||
                numericEndDate1.IsBetween(numericStartDate2, numericEndDate2))
            {
                return true;
            }

            return false;
        } 


if (IsOverlap(startdate1, enddate1, startdate2, enddate2))
            {
                Console.WriteLine("IsOverlap");
            }

Sancho

Mind to add some words of explanation? – Petro

Answer 30

This was my solution, it returns true when the values don't overlap:

X START 1 Y END 1

A START 2 B END 2

TEST1: (X <= A || X >= B)
        &&
TEST2: (Y >= B || Y <= A) 
        && 
TEST3: (X >= B || Y <= A)


X-------------Y
    A-----B

TEST1:  TRUE
TEST2:  TRUE
TEST3:  FALSE
RESULT: FALSE

---------------------------------------

X---Y
      A---B

TEST1:  TRUE
TEST2:  TRUE
TEST3:  TRUE
RESULT: TRUE

---------------------------------------

      X---Y
A---B

TEST1:  TRUE
TEST2:  TRUE
TEST3:  TRUE
RESULT: TRUE

---------------------------------------

     X----Y
A---------------B

TEST1:  FALSE
TEST2:  FALSE
TEST3:  FALSE
RESULT: FALSE

Hexyl

Answer 31

Below query gives me the ids for which the supplied date range (start and end dates overlaps with any of the dates (start and end dates) in my table_name

select id from table_name where (START_DT_TM >= 'END_DATE_TIME'  OR   
(END_DT_TM BETWEEN 'START_DATE_TIME' AND 'END_DATE_TIME'))

Chondroma

Answer 32

I found another quite simple approach. If start and end date of daterange1 falls before start date of daterange2 or start and end date of daterange1 falls after end date of daterange2 this means they don't intersect with each other.

public boolean doesIntersect(DateRangeModel daterange1, DateRangeModel  daterange2) {
    return !(
            (daterange1.getStartDate().isBefore(daterange2.getStartDate())
                    && daterange1.getEndDate().isBefore(daterange2.getStartDate())) ||
                    (daterange1.getStartDate().isAfter(daterange2.getStartDate())
                            && daterange1.getEndDate().isAfter(daterange2.getEndDate())));
}

Munificent

Answer 33

As long as there are simple solutions to this, it might not be efficient to use additional packages just to do the simple stuff. However, if you are already using date-fns already in your project, there is a method called areIntervalsOverlapping which does the same thing.

Syntax:

areIntervalsOverlapping(intervalLeft, intervalRight, [options])

Example:

// For overlapping time intervals:
areIntervalsOverlapping(
  { start: new Date(2014, 0, 10), end: new Date(2014, 0, 20) },
  { start: new Date(2014, 0, 17), end: new Date(2014, 0, 21) }
)
//=> true

Coquito

Answer 34

For covering all the overlapping cases in PHP/Laravel Carbon Package, You can expand logic in Charles' Answer as follows.

if ( ($startTime1->between($startTime2, $endTime2, true) || $endTime1->between($startTime2, $endTime2, true)) || (($startTime1 <= $endTime2) && ($endTime2 <= $endTime1)) ){
//Complete Overlap, Partial Left Overlap, Partial Right Overlap. 
}

This Checks if the StartTime1 is between the range of (StartTime2-EndTime2) Or of EndTime1 is Between the range of (StartTime2-EndTime2).

The Rest of the part for complete overlaps as explained in other answers.

Merry

Answer 35

Here my simplification. Assume intervals

       | |
     | |

dont overlap, xa < xb and ya < yb to simplify the handling. Above conditions can be trivially checked. Then you end up with

      ya       yb
      |--------|
xa |--|xb
   |----|
         |----|
ya > xa => return ya - xa < xb - xa
else    => return xa < yb

As you may notice, the second interval is only relevant in case xa >= ya.

Forland

Answer 36

Here's a TypeScript implementation that I find easy to read/reason-about. I've written some unit tests against it, and it behaves as I would expect.

type DateRange = {
  start: Date;
  end: Date;
};

const doDateRangesOverlap = (a: DateRange, b: DateRange) => {
  // First, let's figure out which date range starts earlier.
  const [earlierStart, laterStart] = a.start < b.start ? [a, b] : [b, a];

  // Now we can just do a simple check - does the earlier date range
  // end after the later date range has begun?
  return earlierStart.end > laterStart.start;
};

Pekingese

Answer 37

Here is a generic method that can be usefull locally.

    // Takes a list and returns all records that have overlapping time ranges.
    public static IEnumerable<T> GetOverlappedTimes<T>(IEnumerable<T> list, Func<T, bool> filter, Func<T,DateTime> start, Func<T, DateTime> end)
    {
        // Selects all records that match filter() on left side and returns all records on right side that overlap.
        var overlap = from t1 in list
                      where filter(t1)
                      from t2 in list
                      where !object.Equals(t1, t2) // Don't match the same record on right side.
                      let in1 = start(t1)
                      let out1 = end(t1)
                      let in2 = start(t2)
                      let out2 = end(t2)
                      where in1 <= out2 && out1 >= in2
                      let totover = GetMins(in1, out1, in2, out2)
                      select t2;

        return overlap;
    }

    public static void TestOverlap()
    {
        var tl1 = new TempTimeEntry() { ID = 1, Name = "Bill", In = "1/1/08 1:00pm".ToDate(), Out = "1/1/08 4:00pm".ToDate() };
        var tl2 = new TempTimeEntry() { ID = 2, Name = "John", In = "1/1/08 5:00pm".ToDate(), Out = "1/1/08 6:00pm".ToDate() };
        var tl3 = new TempTimeEntry() { ID = 3, Name = "Lisa", In = "1/1/08 7:00pm".ToDate(), Out = "1/1/08 9:00pm".ToDate() };
        var tl4 = new TempTimeEntry() { ID = 4, Name = "Joe", In = "1/1/08 3:00pm".ToDate(), Out = "1/1/08 8:00pm".ToDate() };
        var tl5 = new TempTimeEntry() { ID = 1, Name = "Bill", In = "1/1/08 8:01pm".ToDate(), Out = "1/1/08 8:00pm".ToDate() };
        var list = new List<TempTimeEntry>() { tl1, tl2, tl3, tl4, tl5 };
        var overlap = GetOverlappedTimes(list, (TempTimeEntry t1)=>t1.ID==1, (TempTimeEntry tIn) => tIn.In, (TempTimeEntry tOut) => tOut.Out);

        Console.WriteLine("\nRecords overlap:");
        foreach (var tl in overlap)
            Console.WriteLine("Name:{0} T1In:{1} T1Out:{2}", tl.Name, tl.In, tl.Out);
        Console.WriteLine("Done");

        /*  Output:
            Records overlap:
            Name:Joe T1In:1/1/2008 3:00:00 PM T1Out:1/1/2008 8:00:00 PM
            Name:Lisa T1In:1/1/2008 7:00:00 PM T1Out:1/1/2008 9:00:00 PM
            Done
         */
    }

Laue

Answer 38

if (StartDate1 > StartDate2) swap(StartDate, EndDate);

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

Photoactinic

Second line is sufficient. What is the purpose of the first line? What are StartDate and EndDate it refers to? – Ignatzia

Answer 39

Easy solution:

compare the two dates: 
    A = the one with smaller start date, B = the one with bigger start date
if(A.end < B.start)
    return false
return true

Courteous

Answer 40

a compact formula that works for me

class ValidityRuleRange {
        private final Date from;
        private final Date to;
    ...
    private boolean isOverlap(ValidityRuleRange vrr) {
        int c1 = from.compareTo(vrr.getTo());
        int c2 = to.compareTo(vrr.getFrom());
        return c1 == 0 || c2 == 0 || c1 + c2 == 0;
    }

Gyp