MongoDB - Check if Date Range A overlaps other Date Ranges b

Question

I've got a collection booking. Inside that Collection i've got for debug one document:

{
"_id": ObjectID("55d7608120b345d2cc7c9f45"),
"name": "booking1",
"start": ISODate("2015-01-10T00:00:00.000Z"),
"end": ISODate("2015-01-20T00:00:00.000Z")}

Now I have a second Date Range and want to check If the Date Range B overlaps in one of the booking documents.

My Query is:

db.booking.find({$or:[{"start":{$gt:ISODate("2015-01-15T00:00:00.000Z")}},{"end":{$lt:ISODate("2015-01-25T00:00:00.000Z")}}]});

When I execute it Sometimes I get a result and When i change the Date in the query I get no result. It's not logic! I don't know if the query is correct or i should change mit tactic.

My App is a Node JS + Express + MongoClient environment.

I hope for answers, Thank you All Greetings DroidSheep

Answer 1

How about this query?

db.booking.find({ $or: [ 
  { start : { $lte: ISODate("2015-01-15T00:00:00.000Z") }, end : { $gte: ISODate("2015-01-15T00:00:00.000Z") } },
  { start : { $lte: ISODate("2015-01-25T00:00:00.000Z") }, end : { $gte: ISODate("2015-01-25T00:00:00.000Z") } },
  { start : { $gt: ISODate("2015-01-15T00:00:00.000Z") }, end : { $lt: ISODate("2015-01-25T00:00:00.000Z") } }
]});

It checks that the start of Date Range B is within the queried document date range, or the end of Date Range B is within the queried document date range, or that Date Range B starts before and ends after the queried document date range.

Answer 2

I've tried it it looks good but when i perform this query:

{ $or: [ 
  { start : { $lte: ISODate("2015-01-5T00:00:00.000Z") }, end : { $gt: ISODate("2015-01-5T00:00:00.000Z") } },
  { start : { $lt: ISODate("2015-01-10T00:00:00.000Z") }, end : { $gte: ISODate("2015-01-10T00:00:00.000Z") } },
  { start : { $gt: ISODate("2015-01-5T00:00:00.000Z") }, end : { $lt: ISODate("2015-01-10T00:00:00.000Z") } }
]}

The mongo doesn't respond with my document. I've tried it with multiple solutions but the mongo didn't show me the document! But thank you for the first query sheilak. Greetings DroidSheep

EDIT: My Document:

{
    "_id": ObjectID("55d78c3720b345d2cc7c9f49"),
    "name": "test_booking",
    "start": ISODate("2015-01-10T00:00:00.000Z"),
    "end": ISODate("2015-01-20T00:00:00.000Z")
}

EDIT EDIT: I did a workaround like this:

{ $or: [ 
  { start : { $lte: ISODate("2015-01-01T00:00:00.000Z") }, end : { $gt: ISODate("2015-01-01T00:00:00.000Z") } },
  { start : { $lt: ISODate("2015-01-10T00:00:00.000Z") }, end : { $gte: ISODate("2015-01-10T00:00:00.000Z") } },
  { start : { $gt: ISODate("2015-01-01T00:00:00.000Z") }, end : { $lt: ISODate("2015-01-10T00:00:00.000Z") } },
  { start : ISODate("2015-01-10T00:00:00.000Z")},
  { end :  ISODate("2015-01-01T00:00:00.000Z")}
]}

Is it right? Can you check it please? Greetings DroidSheep