CODEWITHSUNDEEP
mongodb
Leos Literak
Leos Literak

Reputation: 9474

Mongo date range search misses some data

I have a database of stats day by day. I want to select documents in some range. But I receive weird data. I do not know if it is related to my timezone (CET). Mongo return GMT+0 timezone which makes the display harder to read, but anyway the count should be stable.

I want data between Aug 31 and Sep 3rd inclusive, so I use $gte and $lte operands. I should receive 4 documents, but get 3:

> db.accidents.find({date: {$gte : ISODate('2020-08-31'), $lte: ISODate('2020-09-03')}}, {date: 1});
{ "_id" : ObjectId("5f663f880bbf0762e87e9026"), "date" : ISODate("2020-08-31T22:00:00Z") }
{ "_id" : ObjectId("5f663fe37c05530dd0166906"), "date" : ISODate("2020-09-01T22:00:00Z") }
{ "_id" : ObjectId("5f6707f8eaea0e3478554dee"), "date" : ISODate("2020-09-02T22:00:00Z") }

Another example, I want two days:

> db.accidents.find({date: {$gte : ISODate('2020-09-01'), $lte: ISODate('2020-09-02')}}, {date: 1});
{ "_id" : ObjectId("5f663fe37c05530dd0166906"), "date" : ISODate("2020-09-01T22:00:00Z") }

From curiosity I changed the second operand to $lt and the result is the same. And correct, actually.

> db.accidents.find({date: {$gte : ISODate('2020-09-01'), $lt: ISODate('2020-09-02')}}, {date: 1});
{ "_id" : ObjectId("5f663fe37c05530dd0166906"), "date" : ISODate("2020-09-01T22:00:00Z") }

And finally lets create a query that shall not find anything:

> db.accidents.find({date: {$gt : ISODate('2020-09-01'), $lt: ISODate('2020-09-02')}}, {date: 1});
{ "_id" : ObjectId("5f663fe37c05530dd0166906"), "date" : ISODate("2020-09-01T22:00:00Z") }

What's going on there?

Upvotes: 0

Views: 119

Answers (1)

Montgomery Watts
Montgomery Watts

Reputation: 4034

If you want to get all dates from Aug 31 and Sep 3rd inclusive, you should use $lt on the upper bound, like this:

db.accidents.find({date: {$gte : ISODate('2020-08-31'), $lt: ISODate('2020-09-04')}}, {date: 1});

Notice that I'm passing a date that is a day after your upper bound.

When passing in a date in with $lt, you should keep in mind that you're comparing the values to a single point in time. So when you did this:

db.accidents.find({date: {$gte : ISODate('2020-09-01'), $lt: ISODate('2020-09-02')}}, {date: 1});

You're actually getting all documents with a date greater than or equal to midnight September 1st, and less than midnight September 2nd. That's why you won't get any documents from September 2nd that aren't exactly at midnight.

Upvotes: 1

Related Questions