Mongoose date comparison does not return correct result

Question

I'm trying to get all records between two dates.

This is the where clause I am using:

.where(
{
  'targetDate' : {
    '$gte' : smallBound,
    '$lte' : highBound
  }
});

Where smallBound and highBound are Date objects with values equal to

smallBound : Fri Jan 05 2018 00:00:00 GMT+0000 (UTC)
highBound : Sat Jan 06 2018 00:00:00 GMT+0000 (UTC)

I have records on dates: Fri Jan 05 2018 00:00:00 GMT+0000 (UTC) and Sat Jan 06 2018 00:00:00 GMT+0000 (UTC)

However, the request only returns one result (I'd expect 2)

What am I doing wrong?

I get only the 2018-01-06T00:00:00.000Z record.

If ever I try with the lower lower bound Thu Jan 04 2018 00:00:00 GMT+0000 (UTC) I do get the 2018-01-05T00:00:00.000Z record

Here is the Mongoose debug log:

**Insertion of the objects: **

Mongoose: clips.insert({ creatorId: ObjectId("5a38e6ae10aa8ae4f66a5ee1"), createdAt: new Date("Fri, 05 Jan 2018 00:00:00 GMT"), updatedAt: new Date("Fri, 05 Jan 2018 00:00:00 GMT"), targetDate: new Date("Fri, 05 Jan 2018 00:00:00 GMT"), type: 'movie', _id: ObjectId("5a38e6ae10aa8ae4f66a5ee6"), __v: 0 }) Mongoose: clips.insert({ creatorId: ObjectId("5a38e6ae10aa8ae4f66a5ee1"), createdAt: new Date("Sat, 06 Jan 2018 00:00:00 GMT"), updatedAt: new Date("Sat, 06 Jan 2018 00:00:00 GMT"), targetDate: new Date("Sat, 06 Jan 2018 00:00:00 GMT"), type: 'movie', _id: ObjectId("5a38e6ae10aa8ae4f66a5ee7"), __v: 0 })

Search query:

Mongoose: clips.find({ creatorId: ObjectId("5a38e6ae10aa8ae4f66a5ee1"), targetDate: { '$gte': new Date("Fri, 05 Jan 2018 00:00:00 GMT"), '$lte': new Date("Sat, 06 Jan 2018 00:00:00 GMT") } }, { skip: 1, limit: 20, sort: { targetDate: 1, _id: 1 }, fields: {} })

What record matches out the two? Based on what you have said both should match given it's <= / >= comparison so I'd start to question whether you really do have the correct data... — James
@James The upper bound record only. I edited the question with some details. It really seems that my data is correct, or that I misunderstood something about the data... maybe timezones issue ? — Antzi
Sounds like a timezone issue to me. You might want to check your server log to see what query actually hits the server. You may find the times to be off by some hours... stackoverflow.com/questions/15204341/… — dnickless
@dnickless this was my initial presumption given reducing the lower bound by 24 hours it finds the record. However, according to the OP dates are UTC... this is why I was asking whether the data really is accurate. — James

Antzi Antzi · Accepted Answer · 2017-12-19T10:38:05

The debug query shows the issue: skip: 1 is the culprit.

Obviously the first record is always discarded.

Mongoose date comparison does not return correct result

1 Answers