Search code examples
node.jsdatabasemongodbmongoosenosql

mongoose query to find if startDate and endDate is intersecting with any object from array of startDates and endDates objects

I'm trying to find if a specific startDate and endDate is overlapping with any of the date objects in mongoDb.

Here's the data in db enter image description here

Please note this is an array of objects.

now I've something date like this

init Date >>>>>>>>>>>>>>>>> 2023-03-23T04:03:49.642Z

Ending Date >>>>>>>>>>>>>>>>> 2023-04-10T04:03:49.642Z

Now I need to return this document if it's overlapping else return nothing.

Here's what I been trying to do;

  const getAvailabilityDetails = await Products.find({
    _id: pid,
    bookings: {
      $elemMatch: {
        // $and: [
          productBookingStartDate: { $lt: new Date(initDate.toString()).toISOString() },
          productBookingCompleteDate: { $gt: endingDayBufferCalcWithContractDays }
        // ]
      }
    }
  });

It's not working at any case. In debug query I'm getting something like this.

Mongoose: products.find({ _id: ObjectId("63de4823561e0319a5745537"), bookings: { '$elemMatch': { productBookingStartDate: { '$lt': new Date("Thu, 23 Mar 2023 04:03:49 GMT") }, productBookingCompleteDate: { '$gt': new Date("Mon, 10 Apr 2023 04:03:49 GMT") } } }}, { projection: {} })

Can anyone please help me to fix this issue.

Solution

  • For checking overlap between time range a->b and time range c->d we need to accept 4 options: [acdb, cabd, cadb, acbd] and reject two options: [abcd, cdab], So we want to find cases where c < b and d > a:

    db.collection.find({
  _id: pid,
  bookings: {
    $elemMatch: {
      productBookingCompleteDate: {$gt: new Date(initDate)},
      productBookingStartDate: {$lt: new Date(endDate)}
    }
  }
})

    See how it works on the playground example