How to query date between in Mongoose

Question

I have a set of records in my Mongo database.

2023-09-12T00:10:17.558+00:00
2023-09-12T00:10:45.515+00:00
2023-09-12T00:10:49.121+00:00
2023-09-12T00:12:59.252+00:00
2023-09-12T23:23:45.238+00:00
2023-09-12T23:23:46.170+00:00
2023-09-12T23:34:43.082+00:00
2023-09-13T06:40:37.457+00:00

I'm querying it using the Mongoose find

const findActivityLogsByFilter = async (filter: TEmployeeFilter, currentUser: IEmployeeAttributes) => {
  try {
    console.log(filter.startDate, filter.endDate);
    return await employee_activity_logsModel
      .find({
        employeeId: filter.employeeId,
        activity: { $in: filter.actions },
        logDate: { $gte: filter.startDate, $lte: filter.endDate },
        _discontinuedUser: null,
      })
      .sort({ _id: 'desc' })
      .exec();
  } catch (error) {
    throw new InternalServerError(messages.generalMessage.Error);
  }
};

All the records are shown if I leave the employeeId and activity alone in the filter. But when I put the logDate filter. there is no result showing

I tried different date formats such as

fromDate: 2023-09-12, toDate: 2023-09-12

fromDate: Tue Sep 12 2023 16:04:46 GMT+0800 (Taipei Standard Time),
toDate: Tue Sep 12 2023 16:04:46 GMT+0800 (Taipei Standard Time)

and even use moment to format the date to use on the from and to date filter but there is no result shown.

I also tried to query it directly on the MongoDB Compass but had no luck.

{
  employeeId:ObjectId('64f9731b4f8f4f269a44e7af'), 
  logDate:{
    $gte:ISODate('2023-09-12'),
    $lte:ISODate('2023-09-12')
  }
}

Is there a format of date I need to use in order to query the date regardless of time? What I'm expecting is to get all the records with the date 2023-09-12 because my query is fromDate: 2023-09-12, toDate: 2023-09-12.

Answer 1

As you filter the document with the dates with startDate: new Date("2023-09-12") and endDate: new Date("2023-09-12") with the query:

logDate: { $gte: startDate, $lte: endDate }

The query will filter the document with logDate within "2023-09-12T00:00:00Z" to "2023-09-12T00:00:00Z". This will only match the documents with logDate: "2023-09-12T00:00:00Z".

You should add 1 day to the endDate and use the $lt:

filter.endDate.setDate(filter.endDate.getDate() + 1);

logDate: { $gte: filter.startDate, $lt: filter.endDate }

Hence the query results will become

logDate: { $gte: new Date("2023-09-12"), $lt: new Date("2023-09-13") }

to get the documents with logDate is on "2023-09-12".