Mongodb query to select all documents between two dates

Question

I am currently struggling with this topic and I tried multiple solution from this platform like this one and found no results.

Basically I have an endpoint in a nestjs application that receives via a POST request a from and a to parameter that are both dates(string that can be converted with new Date(from/to)).

I am trying to get all documents in the database between those two dates(from and to).

Here is the query that I am making:

const data = await model.find( {
        payload: {
          timestamp: {
            $gte: new Date( from ).toISOString(),
            $lte: new Date( to ).toISOString()
          }
      }
} );

The response that I get is this error:

"Cast to date failed for value \"{ '$gte': 2023-01-03T14:15:52.767Z, '$lte': 2023-01-03T14:16:52.767Z }\" (type Object) at path \"timestamp\" for model \"data\"",

I also tried to get put the timestamp key outside the payload object so:

With error:

{payload:{timestamp: ... }}

query:

const data = await model.find( {
        payload: {
          timestamp: {
            $gte: new Date( from ).toISOString(),
            $lte: new Date( to ).toISOString()
          }
      }
} );

Without error but I get an empty array every time:

{payload:{}, timestamp: ...}

query:

const data = await model.find( {
   timestamp: {
       $gte: new Date( from ).toISOString(),
       $lte: new Date( to ).toISOString()
   }
} );

What exactly am I doing wrong?

How is timestamp defined in the schema? If it is a date, try without the `toISOString()` — Joe, Jan 03 '23 at 22:41

score 0 · Accepted Answer · answered Jan 03 '23 at 23:07

Using the discussion on this issue as the clue, the problem is in the query syntax here. Since you are using embedded fields, the query should use dot notation:

const data = await model.find( {
  'payload.timestamp': {
     $gte: new Date( from ).toISOString(),
     $lte: new Date( to ).toISOString()
  }
} );

With the current syntax Mongoose is trying to construct a date object which is causing the Cast to date failed for value ... (type Object) error. This syntax isn't what you'd want anyway as the database would be searching and comparing with the object which has different semantics than what you are likely looking for.

Edit to add that more details about the query semantics on this topic can be found here in the documentation.

This is actually a realy good solution. Pleas upvode this so more people see this. Cheers mate! — Andy95, Jan 04 '23 at 01:31

Mongodb query to select all documents between two dates

1 Answers1