Question 193848 in Mongodb Question posted in
The official documentation can be found here.

Filter data based on from and until dates filter in node mongoDB

I have a collection in MongoDB database that has some data and would like to filter and find data based on from and until dates.

Sample data

{
  "_id": "64958e4cd25792d403c05e83",
  "dealId": "4981506000014538111",
  "note": "short description 1123",
  "dateTime": "09/24/2022 12:22 AM",
  "nextId": null,
  "__v": 0
},
{
  "_id":"64958e4cd25792d403c05e86",
  "dealId": "4981506000014538111",
  "note": "big description",
  "dateTime": "06/23/2023 05:51 PM",
  "nextId": null
  "__v": 0
}

I used the following command to fetch data including from and until dates but it did not give me any results. Both startTime and endTime are in EST format. Not sure what am I missing. Please guide

const query = {
    $and: [
        { 
            dealId: dealId 
        },
        { 
            nextId: null 
        }
    ]
}; 
if (from && until) {
    query.$and.push({
        dateTime: {
            $gte: "06/23/2023",
            $lte: "09/24/2022"
        }
    })
}
const notes = await NotesModel.find(query);
Tags:

2

Answers


  1. 0

    You should not use dates with string. If you want to query on last week :

    const currentDate= new Date()

from = new Date(currentDate.getFullYear(),
    currentDate.getMonth(),
    currentDate.getDate() + (7 - currentDate.getDay())

if (from && until) {
    query.$and.push({
        dateTime: {
            $gte: from,
            $lte: currentDate
        }
    })
}
    Login or Signup to reply.
  2. 0

    As already stated in the comments, you cannot compare date values when stored as string in local format. MongoDB does not support 12-hour times natively, I suggest to use a 3rd party Date library, e.g. moment.js, Luxon or Day.js and convert the strings to proper Date values. Here an example using Luxon:

    const { DateTime } = require("luxon");
db.NotesModel.find({ dateTime: { $type: "string" } }).toArray().forEach( doc => {
   let ts = DateTime.fromFormat(doc.dateTime, "MM/dd/yyyy hh:mm a").toJSDate();
   db.NotesModel.updateOne({ _id: doc._id }, { $set: { dateTime: ts } })
})

    Depending on the data size, this update may take some time.

    Note, DateTime is parsed at current time zone, you may have to set the applicable time zone explicitly.

    Then you can run your queries:

    db.NotesModel.find({
   dateTime: {
      $gte: ISODate("2023-06-23"),
      $lte: ISODate("2022-09-24")
   }
})

    However, this particular query will never return anything, because a date cannot be later than 2023-06-23 and earlier than 2022-09-24.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search