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.
This question is related to
datetime
math
language-agnostic
The answer is
(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)
if (StartDate1 > StartDate2) swap(StartDate, EndDate);
(StartDate1 <= EndDate2) and (StartDate2 <= EndDate1);
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-------|----------------------
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
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;
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
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) {
...
}
An easy way to remember the solution would be
min(ends)>max(starts)
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
*/
}
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.
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)
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())));
}
I believe that it is sufficient to say that the two ranges overlap if:
(StartDate1 <= EndDate2) and (StartDate2 <= EndDate1)
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")
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-------|----------------------
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!
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.
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
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 its shortened.
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())));
}
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 just ensuring that a specific range starts earlier! You ensure that the first range starts earlier (or at the same time) by swapping the ranges if necessary up front.
Then, you can detect overlap if the other 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).
Assuming inclusive at both ends, there's only four possibilities of which one is a non-overlap:
|----------------------| range 1
|---> range 2 overlap
|---> range 2 overlap
|---> range 2 overlap
|---> range 2 no overlap
The endpoint of the range 2 doesn't enter into it. So, in pseudo-code:
def doesOverlap (r1, r2):
if r1.s > r2.s:
swap r1, r2
if r2.s > r1.e:
return false
return true
This could be simplified even more into:
def doesOverlap (r1, r2):
if r1.s > r2.s:
swap r1, r2
return r2.s <= r1.e
If the ranges are inclusive at the start and exclusive at the end, you just have to replace > with >= in the second if statement (for the first code segment: in the second code segment, you'd use < rather than <=):
|----------------------| range 1
|---> range 2 overlap
|---> range 2 overlap
|---> range 2 no overlap
|---> range 2 no overlap
You greatly limit the number of checks you have to make because you remove half of the problem space early by ensuring range 1 never starts after range 2.
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-------|----------------------
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
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:
All of these use the Half-Open approach to defining a span of time where the beginning is inclusive and the ending is exclusive.
An easy way to remember the solution would be
min(ends)>max(starts)
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
@StartDateand@EndDatefrom 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.
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;
}
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!
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.
)
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);
}
The mathematical solution given by @Bretana is good but neglects two specific details:
- aspect of closed or half-open intervals
- 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.
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 its shortened.
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.
)
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 just ensuring that a specific range starts earlier! You ensure that the first range starts earlier (or at the same time) by swapping the ranges if necessary up front.
Then, you can detect overlap if the other 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).
Assuming inclusive at both ends, there's only four possibilities of which one is a non-overlap:
|----------------------| range 1
|---> range 2 overlap
|---> range 2 overlap
|---> range 2 overlap
|---> range 2 no overlap
The endpoint of the range 2 doesn't enter into it. So, in pseudo-code:
def doesOverlap (r1, r2):
if r1.s > r2.s:
swap r1, r2
if r2.s > r1.e:
return false
return true
This could be simplified even more into:
def doesOverlap (r1, r2):
if r1.s > r2.s:
swap r1, r2
return r2.s <= r1.e
If the ranges are inclusive at the start and exclusive at the end, you just have to replace > with >= in the second if statement (for the first code segment: in the second code segment, you'd use < rather than <=):
|----------------------| range 1
|---> range 2 overlap
|---> range 2 overlap
|---> range 2 no overlap
|---> range 2 no overlap
You greatly limit the number of checks you have to make because you remove half of the problem space early by ensuring range 1 never starts after range 2.
if (StartDate1 > StartDate2) swap(StartDate, EndDate);
(StartDate1 <= EndDate2) and (StartDate2 <= EndDate1);
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");
}
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.
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;
}
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
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'))
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
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.
I believe that it is sufficient to say that the two ranges overlap if:
(StartDate1 <= EndDate2) and (StartDate2 <= EndDate1)
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
Using Java util.Date, here 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;
}
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)
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");
}
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.
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
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);
}
def if_lives_overlap(s1, s2):
"""
https://stackoverflow.com/a/325964/827391
5 10 15 20
----------------
s1: -----
s2: -----
>>> if_lives_overlap(s1={'start': datetime(2020, 1, 5),
... 'end': datetime(2020, 1, 10)},
... s2={'start': datetime(2020, 1, 15),
... 'end': datetime(2020, 1, 20)})
False
s1: -----
s2: -----
>>> if_lives_overlap(s1={'start': datetime(2020, 1, 15),
... 'end': datetime(2020, 1, 20)},
... s2={'start': datetime(2020, 1, 5),
... 'end': datetime(2020, 1, 10)})
False
s1: ----------
s2: ----------
>>> if_lives_overlap(s1={'start': datetime(2020, 1, 5),
... 'end': datetime(2020, 1, 15)},
... s2={'start': datetime(2020, 1, 10),
... 'end': datetime(2020, 1, 20)})
True
s1: ----------
s2: ----------
>>> if_lives_overlap(s1={'start': datetime(2020, 1, 10),
... 'end': datetime(2020, 1, 20)},
... s2={'start': datetime(2020, 1, 5),
... 'end': datetime(2020, 1, 15)})
True
s1: ---------------
s2: -----
>>> if_lives_overlap(s1={'start': datetime(2020, 1, 5),
... 'end': datetime(2020, 1, 20)},
... s2={'start': datetime(2020, 1, 10),
... 'end': datetime(2020, 1, 15)})
True
s1: -----
s2: ---------------
>>> if_lives_overlap(s1={'start': datetime(2020, 1, 10),
... 'end': datetime(2020, 1, 15)},
... s2={'start': datetime(2020, 1, 5),
... 'end': datetime(2020, 1, 20)})
True
"""
return (s1['start'] <= s2['end']) and (s1['end'] >= s2['start'])
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
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.
You can try this:
//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);
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);
}
You can try this:
//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);
I believe that it is sufficient to say that the two ranges overlap if:
(StartDate1 <= EndDate2) and (StartDate2 <= EndDate1)
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.
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) {
...
}
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-------|----------------------
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.
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;
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'))
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")
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);
}
The mathematical solution given by @Bretana is good but neglects two specific details:
- aspect of closed or half-open intervals
- 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.
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
*/
}
I believe that it is sufficient to say that the two ranges overlap if:
(StartDate1 <= EndDate2) and (StartDate2 <= EndDate1)
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
@StartDateand@EndDatefrom 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.
Using Java util.Date, here 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;
}
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.
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)
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));
}
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.
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));
}
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.
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:
All of these use the Half-Open approach to defining a span of time where the beginning is inclusive and the ending is exclusive.
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)
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
Source: Stackoverflow.com