Query MongoCollection for data between two Date Strings

Question

Im trying to create a query to search for all collections that are in between of two Date strings, however all my attempts have failed and every time I try a query it always return no results

this is the query im using:

const intialDate = new Date('2024-05-23T06:00:00.000Z');
const endDate = new Date('2024-05-24T20:44:02.172Z');

const dispoquery = this.guestModel.find({
  $and: [
    {
      $or:[
        {
          initial: {
          $gte: intialDate
          }
        },
        {
          end: {
          $lte: endDate
          }
        },
      ]
    }
  ]
}).catch((err) => {
  return err;
})
.then((doc:any)=> {
      return doc
      })

I need all documents that comply this assortment:

intial >= intialDate || end <= endDate 

But I always get no results this is a sample data of my collection:

{
  "folio": "W1013",
  "nombre": "CArlos",
  "initial": "2024-05-23T06:00:00.000Z",
  "end": "2024-05-24T19:52:11.050Z",
  "__v": 0
}

Can someone please tell me what im doing wrong please?

Answer 1

Some issues with your data and query:

1. Your query has the words initial & end but your DB document has llegada & salida.

2. Your documents have the dates as Strings, not as Date Objects. And you are comparing with Date objects, not Strings.

   • So either do it as a String comparison (not recommended) or convert your llegada & salida to dates using $dateFromString.
   • If you will always use only UTC for all date variables and all your documents have date strings which represent UTC then you can do it as a String comparison
   • But if there may be multiple timezones used, then convert them with $dateFromString during the query

3. There's no need to use $and if it's just one $or clause within it.

4. As cmgchess commented, if you are looking for something between two dates, you should only use $and, not $or.

   But your question says

   intial >= intialDate || end <= endDate 
   

   So I'll keep the logic the same as per what you already have. But you should probably do

   intial >= intialDate && end <= endDate 
   

Since some conversions need to be done on the document data while querying, you'll need to use $expr with your comparisons/checks.

db.guestModel.find({
  $expr: {
    $or: [
      {
        $gte: [
          { $dateFromString: { dateString: "$llegada" } },
          intialDate
        ]
      },
      {
        $lte: [
          { $dateFromString: { dateString: "$salida" } },
          endDate
        ]
      }
    ]
  }
})

Mongo Playground