CODEWITHSUNDEEP
mongodb
efx
efx

Reputation: 83

why does $eq comparison is not working on mongodb with dates

I have the following query on mongo

db.getCollection('someCollection').find({"status":"failed","start_date": {$gt: new Date("2019/03/01")}})

that will retrieve all the records that have a failed status and the start_date is equal or greater than "2019/03/01".

But when i try to only retrieve records specifically for "2019/03/01":

db.getCollection('someCollection').find({"status":"failed","start_date": {$eq: new Date("2019/03/01")}})

It doesn't retrieve anything.

$lt and $gt queries work is just $eq that doesn't work. is that the correct way to use the $eq?

thank you

Upvotes: 2

Views: 2480

Answers (1)

Hashcut
Hashcut

Reputation: 843

When you use new Date("2019/03/01") the actual date being searched is 2019/03/01 00:00:00.00. That is, the Date is exactly midnight 2019/03/01 (down to the millisecond). Unless your record also has the exact same date recorded, it will not match. That's why you need to use {$gt: new Date("2019/03/01"), $lt: new Date("2019/03/02")}. Just think of it as that 1 "day" is actually a range of 24 hours. Which is why you need to specify it as a range, and not as a simple $eq comparison.

The reason why your first query works is because when you search for $gt 2019/03/01 you're really searching for $gt 2019/03/01 00:00:00.00. And so, of course every record started on 2019/03/01 will match.

One thing to take note of: technically, if you have a document made exactly at midnight i.e. with a timestamp of 2019/03/01 00:00:00.00, it won't match. So you really should use {$gte: new Date ('2019/03/01')} (note the extra 'e'). $gte is greater than or equal. This may sound trivial but is actually important, because if you don't use actual timestamps and record just the date when you create the record (i.e. you insert with {start_date: new Date('2019/03/01')}, all those timestamps will actually have 00:00:00.00 as their time component, and they won't match with a $gt comparison, only a $gte comparison. You probably use new Date() when creating the record, so you're getting the full timestamp, which is why it hasn't bitten you yet. But to be semantically correct, you should use $gte in your first query. Does that make sense?

Upvotes: 2

Related Questions