Określ, czy dwa zakresy dat pokrywają się

_ |---- DateRange A ------| 

|---Date Range B -----| _

StartA & > EndB

|---- DateRange A -----| _ 

 _ |---Date Range B ----|

EndA & < StartB

Not (A Or B)

Not A And Not B

(StartA & < = EndB) i (EndA & > = StartB)

& > =

& >

& < =

& <

endA-startA

endA - startB

endB-startA

endB - startB

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

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

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

startA & > endA

startB & > endB

(PoczątekA & < = KoniecB) i (PoczątekB & < = KoniecA) i (PoczątekA & < = KoniecA) i (PoczątekB & < = KoniecB)

(StartA & < = KoniecB) i (PoczątekA & < = KoniecA) i (PoczątekB & < = KoniecA) i (PoczątekB & < = KoniecB)

(StartA & < = Min (KoniecA, KoniecB) i (PoczątekB & < = Min (KoniecA, KoniecB))

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

Min ()

Max ()

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

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

// ------------------------------------------------------------------------

public enum PeriodRelation

{

 After,

 StartTouching,

 StartInside,

 InsideStartTouching,

 EnclosingStartTouching,

 Enclosing,

 EnclosingEndTouching,

 ExactMatch,

 Inside,

 InsideEndTouching,

 EndInside,

 EndTouching,

 Before,

}// enum PeriodRelation

A

B

.start

.end

.start & < = .end

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

& > =

& >

& < =

& <

----------------------|-------A-------|----------------------

 |----B1----|

 |----B2----|

 |----B3----|

 |----------B4----------|

 |----------------B5----------------|

 |----B6----|

----------------------|-------A-------|----------------------

 |------B7-------|

 |----------B8-----------|

 |----B9----|

 |----B10-----|

 |--------B11--------|

 |----B12----|

 |----B13----|

----------------------|-------A-------|----------------------

overlap = max(0, min(EndDate1, EndDate2) - max(StartDate1, StartDate2))

if (overlap > 0) { 

 ...

}

|----------------------| range 1

|---> range 2 overlap

 |---> range 2 overlap

 |---> range 2 overlap

 |---> range 2 no overlap

def doesOverlap (r1, r2):

 if r1.s > r2.s:

 swap r1, r2

 if r2.s > r1.e:

 return false

 return true

def doesOverlap (r1, r2):

 if r1.s > r2.s:

 swap r1, r2

 return r2.s <= r1.e

& >

& > =

if

& <

& < =

|----------------------| range 1

|---> range 2 overlap

 |---> range 2 overlap

 |---> range 2 no overlap

 |---> range 2 no overlap

• Traktuje wartości null jako nieskończoność
• Zakłada się, że dolna granica obejmuje całość, a górna granica jest wyłączna.
• W zestawie kilka testów

/**

 * 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);

}

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

 });

});

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

}

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

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

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

IsBetween

public static bool IsBetween(this DateTime value, DateTime left, DateTime right) {

 return (value > left && value < right) || (value < left && value > right);

 }

min(ends)>max(starts)

// 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;

function isOverlapping(startDate1, endDate1, startDate2, endDate2){ 

return moment(startDate1).isSameOrBefore(endDate2) && 

moment(startDate2).isSameOrBefore(endDate1);

}

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 @OverlapEND

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

A = [StartA, EndA]

B = [StartB, EndB] [---- DateRange A ------] (True if StartA > EndB)

[--- Date Range B -----] 

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

 [--- Date Range B ----]

(StartA & < = EndB) i (EndA & > = StartB)

A = (StartA, EndA)

B = (StartB, EndB) (---- DateRange A ------) (True if StartA >= EndB)

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

 (--- Date Range B ----)

(początekA & < koniecB) i (koniecA & > początekB)

A = [StartA, EndA)

B = [StartB, EndB) [---- DateRange A ------) (True if StartA >= EndB) 

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

 [--- Date Range B ----)

(początekA & < koniecB) i (koniecA & > początekB)

A = (StartA, EndA]

B = (StartB, EndB] (---- DateRange A ------] (True if StartA >= EndB)

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

 (--- Date Range B ----]

(początekA & < koniecB) i (koniecA & > początekB)

A = [StartA, EndA)

B = [StartB, EndB] [---- DateRange A ------) (True if StartA > EndB)

[--- Date Range B -----] 

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

 [--- Date Range B ----]

(początek A & < = koniec B) i (koniec A & > początek B)

someInterval.overlaps( anotherInterval )

java.time

Interval

language-agnostic

Interval

org.threeten.extra.Interval

java.time.Instant

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

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

Interval

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

Interval

Duration

Instant start_B = Instant.parse( "2016-01-03T00:00:00Z" );

Interval interval_B = Interval.of( start_B , Duration.of( 3 , ChronoUnit.DAYS ) );

Boolean overlaps = interval_A.overlaps( interval_B );

Interval

Interval

Instant

• abuts

  http://www.threeten.org/threet ... rval-

• contains

  http://www.threeten.org/threet ... tant-

• encloses

  http://www.threeten.org/threet ... rval-

• equals

  http://www.threeten.org/threet ... ject-

• isAfter

  http://www.threeten.org/threet ... rval-

• isBefore

  http://www.threeten.org/threet ... rval-

• overlaps

  http://www.threeten.org/threet ... rval-

Half-Open

(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

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)

• aspekt rozstawu zamkniętego lub półotwartego
• puste miejsca

MomentInterval a = MomentInterval.between(Instant.now(), Instant.now().plusSeconds(2));

MomentInterval b = a.collapse();// make b an empty interval out of aSystem.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)

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

System.out.println( "endA > startB: " + a.getEndAsInstant().isAfter(b.getStartAsInstant()));// trueSystem.out.println("a overlaps b: " + a.intersects(b));// a overlaps b: false

// 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

 */

 }

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");

 }

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;

 }

(startB <= startA && endB > startA)

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