Searching a mongodb date field

JavaScript
1

I have a MongoDB with a date field - created_on

created_on is populated when the doc is created via a mongoose schema

"created_on": {
   'type': Date,
   'default': new Date()
},

I am trying to search the database by created_on which is passed in as a query parameter.

// convert string to Date type 2019-08-27
if (req.query.hasOwnProperty("created_on")){        
   req.query.created_on = new Date(req.query.created_on); // 2019-08-27T18:44:39.997Z     
};

If I pass in the exact date: 2019-08-27 18:44:39.997 it does not work.

Can anyone tell me how to search the created_on field?

2

If you’re using mongoose, which I assume, you can do something like this:

if (req.query.hasOwnProperty("created_on")){        
   req.query.created_on = new Date(req.query.created_on); 
}; 

YourModel.find({ 
  created_on: { $eq: req.query.created_on } 
})
.then(data => ...)

That’s a raw example. Since it looks like the created_on parameter is optional, it’d better to build a dynamic query (i.e object that will go into find) and then run YourModel.find function. It might break or give invalid results as per the above example.

1 Like
3

That is exactly what I have done…
I have build a dynamic query and am passing that into the cond of a Model aggregate.

It all works perfectly except the date fields need a little prep before putting them into the dynamic query.

Currently my query looks like:
[
{ ‘$eq’: [ ‘$$item.created_on’, 2019-08-27T00:00:00.000Z ] },
{ ‘$eq’: [ ‘$$item.assigned_to’, ‘bob’ ] }
]

I can’t ask users to enter a date AND time.
T00:00:00.000Z just isn’t feasible for users.

But entering 2019-08-27 18:44:39.997 (which is in the database) doesn’t get found.

4

If you’re not using the time portion, why save your created_on with it?

Something like new Date().setHours(0, 0, 0) will retain what you care about, and standardize the rest to zero. I would imagine that makes an exact search easier given the user input you’re expecting.

Your query is using an actual Date object (which I believe is the type specified in your database) not its string representation (displayed above), right?

If your users aren’t all in the server timezone (likely?), that may introduce other concerns.

5

I never thought of that.

6

Uhm ^^

[
{ ‘$eq’: [ ‘$$item.created_on’, 2019-08-27T00:00:00.000Z ] },
{ ‘$eq’: [ ‘$$item.assigned_to’, ‘bob’ ] }
]

Are you passing these values literally ?

This is what i am using and it works fine

date:{$gte: new Date(from), $lte: new Date(to)}
where from and to are originally date objects, stringified when sent to backend^^

EDIT:
Ah, just realized @partylich had the same doubts :stuck_out_tongue:
I’ll add this link if you were not aware of it ( will set automatically the field you’re setting manually plus updatedAt) :
mongoose schemas- timestamp

1 Like
7

yes, I am right now and it’s succeeding, but not ideal.

When I did the exercise tracker project I filtered the results array after it was returned from the database.
This time for the issue tracker project I wanted to try and do the filtering on the database query by using an aggregate. This has proven really testing. Nearly broke me this week. :unamused:

This timestamp option is new to me and it looks like a better solution. Thanks for this @Layer. I’ll probably swap over to using it

1 Like
8

yes, I am right now and it’s succeeding, but not ideal.

Oh, kk^^
Anyway the whole point is to use Date obj based on a standardized string you can obtain in different ways,
good luck!

This time for the issue tracker project I wanted to try and do the filtering on the database query by using an aggregate.This has proven really testing. Nearly broke me this week. :unamused:

Keep going, aggregation is really powerful :smiley: The excerpt is actually a part of an aggregation query^^

const votations = await rcvColl.aggregate([ { $match: { date:{$gte: new Date(from), $lte: new Date(to)} } }, [...]