Search code examples
databasemongodbdatemongodb-compassnon-relational-database

Mongodb has a bug on "$lte" (query or aggregation) while searching for dates ranges

Scenario:

I have a db hosted on MongoDb Atlas.

This db has a collection which, among other data, has a created field of type Date.

pseudoCode Schema:
... {
created: { type: Date }
}...

I want to perform a query that allows me to find all the objects existing in the collection which have a created value between specifics days including the boundary dates.

Let's assume that the date range is 2020-08-01 and 2020-08-31, the query would be

{created: {'$gte': new Date('2020-08-01'), '$lte': new Date('2020-08-31')}}

right?

Wrong.

By doing the query this way, I only get results that are greater than or equal to "2020-08-01" and lower than "2020-08-31". Meaning that, even if I'm performing an $lte query, I always get the $lt results.

Tests I did

I've tested this only for a type Date field atm on different collections and having consistently the same issue. Didn't have time for further investigations on different data types yet.

I've tested it on aggregation $match pipelines and find queries on:

  • my codebase
  • a clean script that just does this operations
  • directly on MongoDb Compass

In all 3 cases, the results are consisent with the problem exposed and confirm the problem.

Quick fix

Simply use $lt instead of $lte and always consider 1 day more than you intended. Using the previous example, the query will become

{created: {'$gte': new Date('2020-08-01'), '$lt': new Date('2020-09-01')}}

and in this case, I'm getting the expected date range "2020-08-01" - "2020-08-31" results.

Note that I could have also used $lte and I would get the exact same results however, the $lt form is logically more correct for whom is reading the code.

Why I'm posting this

I did find few people have posted about this issue across the years, more relevant links are this GitHub issue (initially the dev believed the problem was with mongoose, then a solution proposed to check the schema but that's not the issue since in my case the schema is properly defined and I've tested it on Compass directly) and this google group discussion (the problem is poorly stated and received no answer).

But I did not find a solution.

Even though I've quick fixed the issue, I wanted to point it out better and understand if:

  • I'm doing something wrong and this is the expected behavior
  • there is something I'm doing wrong in my query
  • there is a problem with $lte which need to be addressed properly

Who has ideas?

Solution

  • When you run new Date('2020-08-01') then the result is actually ISODate("2020-08-01T00:00:00Z")

    So

    {created: {'$gte': new Date('2020-08-01'), '$lte': new Date('2020-08-31')}}

    becomes

    {created: {'$gte': ISODate("2020-08-01T00:00:00Z"), '$lte': ISODate("2020-08-31T00:00:00Z")}}

    i.e. day 2020-08-31 is not included. You may also consider time zones if data was inserted as local time and thus not stored as 2020-08-02T00:00:00Z but 2020-08-02T02:00:00Z for example.

    One solution is to add one day and use $lt:

    {created: {'$gte': new Date('2020-08-01'), '$lt': new Date('2020-09-01')}}

    or you can use Moment.js like this:

    {created: {'$gte': new Date('2020-08-01'), '$lte': moment.utc('2020-08-31').endOf('day').toDate()}}

    or perhaps moment.utc('2020-08-01').endOf('month').toDate()