MongoDB find date range if overlap with other dates

Question

I have many documents with the schema shown below each containing (start date, enddate) as shown in the schema below. Is there a simple way to know before saving a new document if the new document startdate, enddate will overlap with previously saved documents startdate, enddate? Thanks

{
    "title" : "",
    "owner" : "",
    "notes" : "",
    "startdate" : "",
    "enddate" : ""
}

Below is the only document currently saved:

Document.(anonymous function) {_id: "FADnPAxRu4Ps5hkLz", 
   title: "Round A", 
   customerid: "QDGvBQhS6vYgZtnRr", 
   startdate: "11/21/2014 7:25 AM"…}
_id: "FADnPAxRu4Ps5hkLz"customerid: "QDGvBQhS6vYgZtnRr"
enddate: "11/30/2014 6:09 AM"
startdate: "11/21/2014 7:25 AM"
title: "Round A"__proto__: Document.(anonymous function)

When I try executing any of the following query on the above document it return nothing, even though there is an obvious overlap here.

db.Projects.find({'startdate': {$lt: '11/25/2014 6:26 PM'}, 'enddate': {$gt: '11/19/2014 6:26 PM'}}, {sort:{time: -1}});

db.Projects.find({'startdate': {$lt: '11/30/2014 6:26 PM'}, 'enddate': {$gt: '11/21/2014 6:26 PM'}}, {sort:{time: -1}});

Answer 1

The time overlap can be illustrated with these 4 cases in the figure below, where S/E is startdate/enddate of the new document and S'/E' is startdate/enddate of any existing document:

  S'                  E' 
  |-------------------|

   S                E 
   |****************|

S'          E' 
|-----------|

      S'          E' 
      |-----------|

              S'          E' 
              |-----------|

In 4 cases we have S'<E and E'>S. The query to find all documents with overlapped time can be:

db.collection.find({"startdate": {"$lt": E}, "enddate": {"$gt": S}})

EDIT:

Your startdate and enddate are in string format, and not lexically ordered, hence can't use "$gt" and "$lt" for comparison. You should convert them to Date type:

db.collection.find().forEach(
  function (e) {
    // convert date if it is a string
    if (typeof e.startdate === 'string') {
       e.startdate = new Date(e.startdate);
    }
    if (typeof e.enddate === 'string') {
       e.enddate = new Date(e.enddate);
    } 
    // save the updated document
    db.collection.save(e);
  }
)

The final query will be:

db.collection.find({"startdate": {"$lt": new Date("E")}, "enddate": {"$gt": new Date("S")}})

Answer 2

With the help of the accepted answer, I was able to fuse together all four cases and came up with a perfect solution. Hope it might help others.

     S                E 
     |****************|
  S1                     E1
  |----------------------|
       S2        E2
       |----------|
 S3          E3
 |-----------|
              S4           E4
              |-------------|

Case 1: The interval completely covers the start date and end date

{ "startDate": { $lte: moment(S1).toDate() } }
{ "endDate": { $gte: moment(E1).toDate() } }

Case 2: The interval is completely inside the start date and end date

{ "startDate": { $gte: moment(S2).toDate() } }
{ "endDate": { $lte: moment(E2).toDate() } }

Case 3: The start date is outside the interval and the end date is inside the interval

{ "endDate": { $gte: moment(S3).toDate() } }
{ "endDate": { $lte: moment(E3).toDate() } }

Case 4: The start date is inside the interval and the end date is outside the interval

{ "startDate": { $gte: moment(S4).toDate() } }
{ "startDate": { $lte: moment(E4).toDate() } }

Combining all four cases with '$and' and '$or' to get the data where at least one case is met.

const query = {};
const datePipeArray = [];
datePipeArray.push({ "$and":[
    { "startDate": { $gte: moment(queryStartDate).toDate() } },
    { "endDate": { $lte: moment(queryEndDate).toDate() } }
]});
datePipeArray.push({ "$and":[
    { "endDate": { $gte: moment(queryStartDate).toDate() } },
    { "endDate": { $lte: moment(queryEndDate).toDate() } }
]});
datePipeArray.push({ "$and":[
    { "startDate": { $gte: moment(queryStartDate).toDate() } },
    { "startDate": { $lte: moment(queryEndDate).toDate() } }
]});
datePipeArray.push({ "$and":[
    { "startDate": { $lte: moment(queryStartDate).toDate() } },
    { "endDate": { $gte: moment(queryEndDate).toDate() } }
]});
query["$or"] = datePipeArray;
db.calendar.find(query)

P.S: moment is optional