CODEWITHSUNDEEP
mongodbdatetime
Giovanni Lovato
Giovanni Lovato

Reputation: 2273

MongoDB date comparison

How MongoDB performs date comparison? I tried a few test on the MongoDB shell:

> db.test.insert( { "dates" : [ new Date("Jul 21, 1983"), new Date("Aug 7, 1999") ] } )
"ok"
> db.test.find()

[ 
  {   "_id" : {   "$oid" : "5201e8b7cc93742c160bb9d8"   },   "dates" : [   "Thu Jul 21 1983 00:00:00 GMT+0200 (CEST)",   "Sat Aug 07 1999 00:00:00 GMT+0200 (CEST)" ]   }
]

Now I'll try to get all objects with a date in dates greater than Aug 30, 2000.

> db.test.find( { "dates" : { $gt : new Date("Aug 30, 2000") } } )

[

]

As expected, the document doesn't match. Using "Aug 30, 1999", instead...

> db.test.find( { dates : { $gt : new Date("Aug 30, 1999") } } )

[ 
  {   "_id" : {   "$oid" : "5201e8b7cc93742c160bb9d8"   },   "dates" : [   "Thu Jul 21 1983 00:00:00 GMT+0200 (CEST)",   "Sat Aug 07 1999 00:00:00 GMT+0200 (CEST)" ]   }
]

The document matches! What am I missing?

Upvotes: 9

Views: 32967

Answers (1)

user1498724
user1498724

Reputation: 206

It seems to be related to the date conversion.

What version of MongoDB are you using? In the online shell of MongoDB I get the following:

> new Date("Jul 21, 1983")
"Thu Jul 21 1983 00:00:00 GMT+0200 (CEST)"
> new Date("Aug 7, 1999")
"Sat Aug 07 1999 00:00:00 GMT+0200 (CEST)"

> new Date("Aug 30, 2000")
"Wed Aug 30 2000 00:00:00 GMT+0200 (CEST)"
> new Date("Aug 30, 1999")
"Mon Aug 30 1999 00:00:00 GMT+0200 (CEST)"

However, if I try in MongoDB 2.2.2

> new Date("Jul 21, 1983")
ISODate("1983-07-20T22:00:00Z")
> new Date("Aug 7, 1999")
ISODate("1999-08-06T22:00:00Z")
> new Date("Aug 30, 2000")
ISODate("2000-08-29T22:00:00Z")
> new Date("Aug 30, 1999")
ISODate("1999-08-29T22:00:00Z")

In your case, It seems that MongoDB is indexing the string version and then performing a basic string comparison which would explain the results your are getting.

Upvotes: 12

Related Questions