MongoDB: Query by date

Question

In a MongoDB database, I have a collection of items, and each item stores its creation date. I need to query this collection by date. I tried:

db.items.findOne({date:{new Date(1285947037*1000)}})

but it isn't returning anything. I got that timestamp using PHP ($date->sec, where $date is a MongoDate object from the database). So what's the right way to do this? Thanks.

Answer 1

You have a minor error in your query; remove the curly brackets around new Date(...) and you are good to go:

db.items.findOne({date: new Date(1285947037*1000)})

Answer 2

The Date constructor expects a timestamp in milliseconds. The 1285947037 timestamp is in seconds, which is why you're multiplying it by 1000.

My guess is that the actual timestamp in the document contains milliseconds, e.g. 1285947037461 (note the 461 at the end). You're multiplying the seconds by 1000, which results in 1285947037000. As you can see, these timestamp aren't equal:

1285947037461 // actual timestamp in MongoDB
1285947037000 // value you calculated

The problem lies with the MongoDate class: it loses the milliseconds precision, as you can read in the documentation. Here's the quote:

[...] this means that any precision beyond milliseconds will be lost when the document is sent to/from the database.

To find the document you're looking for using the timestamp in seconds, you need something like this:

db.foo.findOne(function () {
  // round the stored date to seconds and compare to timestamp
  return parseInt(this.date / 1000) === 1285947037
})

You can get the exact timestamp in milliseconds in the console, by using myDateObject.getTime().