28

I have a collection that looks like this:

{
  _id: ObjectId("50a68673476427844b000001"),
  other fields
}

I want to do a range query to find records between two dates. I know that I can get the date from the ObjectId in the mongo shell var doing this:

var aDate = ObjectId().getTimestamp()

but there isn't a way (as far as I can figure out at the moment) to create an ObjectId consisting of just the timestamp portion - I think my ideal solution is non-functioning mongo shell code would be:

var minDate = ObjectId(new Date("2012-11-10"));
var maxDate = ObjectId(new Date("2012-11-17"));

Use the find with the minDate and MaxDate as the range values.

Is there a way to do this in the SHELL - I'm not interested in some of the driver products.

JohnnyHK
  • 305,182
  • 66
  • 621
  • 471
sc28
  • 455
  • 1
  • 6
  • 8
  • First 8 bytes of mongoid are timestamp in hexadecimal, so you could create a valid ObjectId with first 8 bytes made from date, and rest just zeros, and then do queries similar to this: `{ _id: {$gt: ObjectId("5087e5b106cffca815000000")} }` –  Nov 27 '12 at 22:34
  • There's a good answer for [Can I query MongoDB ObjectId by date?](http://stackoverflow.com/questions/8749971/can-i-query-mongodb-objectid-by-date) which includes an `objectIdWithTimestamp()` JavaScript function. You can save this function in your [.mongorc.js](http://www.mongodb.org/display/DOCS/Overview+-+The+MongoDB+Interactive+Shell#Overview-TheMongoDBInteractiveShell-.mongorc.js) to have it available in your `mongo` shell on startup. – Stennie Nov 27 '12 at 23:11
  • Does this answer your question? [Can I query MongoDB ObjectId by date?](https://stackoverflow.com/questions/8749971/can-i-query-mongodb-objectid-by-date) – Leon Dec 01 '21 at 16:13

2 Answers2

33

You can do that in 2 steps:

 var objIdMin = ObjectId(Math.floor((new Date('1990/10/10'))/1000).toString(16) + "000
0000000000000")
 var objIdMax = ObjectId(Math.floor((new Date('2011/10/22'))/1000).toString(16) + "000
    0000000000000")
 db.myCollection.find({_id:{$gt: objIdMin, $lt: objIdMax}})

or in one step (what is less readable):

db.myCollection.find({_id:{$gt: ObjectId(Math.floor((new Date('1990/10/10'))/1000).toString(16) + "000
    0000000000000"), $lt: ObjectId(Math.floor((new Date('2011/10/10'))/1000).toString(16) + "000
    0000000000000")}})
Skarlinski
  • 2,419
  • 11
  • 28
Kath
  • 1,834
  • 15
  • 17
  • or even better: `function dateFromObj( strDate ) { return ObjectId(Math.floor((new Date( strDate ))/1000).toString(16) + "0000000000000000") }` – Christian Dechery Jul 05 '17 at 18:19
28

using mongo shell:

you can use the ObjectId.fromDate built in method:

db.mycollection.find({_id: {$gt: ObjectId.fromDate( new Date('2017-09-23') ) } });

from Node.js driver:

you can use the solution provided by @jksdua here as follows:

db.mycollection.find({_id: {$gt: ObjectID.createFromTime( Date.now()/1000 ) } });
Mohammed Essehemy
  • 2,006
  • 1
  • 16
  • 20