Can I query MongoDB ObjectId by date?

问题:

I know that ObjectIds contain the date they were created on. Is there a way to query this aspect of the ObjectId?

回答1:

Popping Timestamps into ObjectIds covers queries based on dates embedded in the ObjectId in great detail.

Briefly in JavaScript code:

// This function returns an ObjectId embedded with a given datetime
// Accepts both Date object and string input

function objectIdWithTimestamp(timestamp) {
    // Convert string date to Date object (otherwise assume timestamp is a date)
    if (typeof(timestamp) == \'string\') {
        timestamp = new Date(timestamp);
    }

    // Convert date object to hex seconds since Unix epoch
    var hexSeconds = Math.floor(timestamp/1000).toString(16);

    // Create an ObjectId with that hex timestamp
    var constructedObjectId = ObjectId(hexSeconds + \"0000000000000000\");

    return constructedObjectId
}


// Find all documents created after midnight on May 25th, 1980
db.mycollection.find({ _id: { $gt: objectIdWithTimestamp(\'1980/05/25\') } });

回答2:

In pymongo, it can be done this way:

import datetime
from bson.objectid import ObjectId
mins = 15
gen_time = datetime.datetime.today() - datetime.timedelta(mins=mins) 
dummy_id = ObjectId.from_datetime(gen_time)
result = list(db.coll.find({\"_id\": {\"$gte\": dummy_id}}))

回答3:

Using inbuilt function provided by mongodb drivers in in Node.js lets you query by any timestamp:

var timestamp = Date.now();
var objectId = ObjectID.createFromTime(timestamp / 1000);

Alternatively, to search for records before the current time, you can simply do:

var objectId = new ObjectID(); // or ObjectId in the mongo shell

Source: http://mongodb.github.io/node-mongodb-native/api-bson-generated/objectid.html

回答4:

Since the first 4 bytes of an ObjectId represent a timestamp, to query your collection chronologically, simply order by id:

# oldest first; use pymongo.DESCENDING for most recent first
items = db.your_collection.find().sort(\"_id\", pymongo.ASCENDING)

After you get the documents, you can get the ObjectId\'s generation time like so:

id = some_object_id
generation_time = id.generation_time

回答5:

how to find Find the Command (this date[2015-1-12] to this Date[2015-1-15]):

db.collection.find({_id:{$gt: ObjectId(Math.floor((new Date(\'2015/1/12\'))/1000).toString(16) + \"0000000000000000\"), $lt: ObjectId(Math.floor((new Date(\'2015/1/15\'))/1000).toString(16) + \"0000000000000000\")}}).pretty()

Count the Command (this date[2015-1-12] to this Date[2015-1-15]):

db.collection.count({_id:{$gt: ObjectId(Math.floor((new Date(\'2015/1/12\'))/1000).toString(16) + \"0000000000000000\"), $lt: ObjectId(Math.floor((new Date(\'2015/1/15\'))/1000).toString(16) + \"0000000000000000\")}})

Remove the Command (this date[2015-1-12] to this Date[2015-1-15]):

db.collection.remove({_id:{$gt: ObjectId(Math.floor((new Date(\'2015/1/12\'))/1000).toString(16) + \"0000000000000000\"), $lt: ObjectId(Math.floor((new Date(\'2015/1/15\'))/1000).toString(16) + \"0000000000000000\")}})

回答6:

To get last 60 days old documents in mongo collection i used below query in shell.

db.collection.find({_id: {$lt:new ObjectId( Math.floor(new Date(new Date()-1000*60*60*24*60).getTime()/1000).toString(16) + \"0000000000000000\" )}})

回答7:

You can use $convert function to extract the date from ObjectId starting in 4.0 version.

Something like

$convert: { input: \"$_id\", to: \"date\" } 

You can query on date comparing between start and end time for date.

db.collectionname.find({
  \"$expr\":{
    \"$and\":[
      {\"$gte\":[{\"$convert\":{\"input\":\"$_id\",\"to\":\"date\"}}, ISODate(\"2018-07-03T00:00:00.000Z\")]},
      {\"$lte\":[{\"$convert\":{\"input\":\"$_id\",\"to\":\"date\"}}, ISODate(\"2018-07-03T11:59:59.999Z\")]}
    ]
  }
})

OR

You can use shorthand $toDate to achieve the same.

db.collectionname.find({
  \"$expr\":{
    \"$and\":[
      {\"$gte\":[{\"$toDate\":\"$_id\"}, ISODate(\"2018-07-03T00:00:00.000Z\")]},
      {\"$lte\":[{\"$toDate\":\"$_id\"},ISODate(\"2018-07-03T11:59:59.999Z\")]}
    ]
  }
})

回答8:

If you want to make a range query, you can do it like in this post. For example querying for a specific day (i.e. Apr 4th 2015):

> var objIdMin = ObjectId(Math.floor((new Date(\'2015/4/4\'))/1000).toString(16) + \"0000000000000000\")
> var objIdMax = ObjectId(Math.floor((new Date(\'2015/4/5\'))/1000).toString(16) + \"0000000000000000\")
> db.collection.find({_id:{$gt: objIdMin, $lt: objIdMax}}).pretty()

回答9:

From the documentation:

o = new ObjectId()
date = o.getTimestamp()

this way you have date that is a ISODate.

Look at http://www.mongodb.org/display/DOCS/Optimizing+Object+IDs#OptimizingObjectIDs-Extractinsertiontimesfromidratherthanhavingaseparatetimestampfield. for more information

回答10:

Using MongoObjectID you should also find results as given below

db.mycollection.find({ _id: { $gt: ObjectId(\"5217a543dd99a6d9e0f74702\").getTimestamp().getTime()}});