4

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

DroidSheep
  • 75
  • 8

2 Answers2

4

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.

sheilak
  • 5,833
  • 7
  • 34
  • 43
  • One Moment i gonna try it! :-) On the first look it looks Good – DroidSheep Aug 21 '15 at 20:40
  • I've updated the query so that the matching is inclusive i.e. it matches if date range B starts exactly at the end of the query document date range. Does that give the expected results? – sheilak Aug 21 '15 at 21:14
  • One question so far how has it to be in MySql? – DroidSheep Oct 19 '15 at 18:06
  • What do you mean, like how would you write a query like above but in MySql? Maybe you should open another question as it's quite a while since I wrote a SQL query :) – sheilak Oct 19 '15 at 20:04
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

DroidSheep
  • 75
  • 8