The ObjectId value contains a timestamp. Actually, it begins with a timestamp ( https://docs.mongodb.com/manual/reference/method/ObjectId/#ObjectId ). All ObjectId values have a getTimeStamp() method that returns the creation time ( https://docs.mongodb.com/manual/reference/method/ObjectId.getTimestamp/#objectid-gettimestamp ). By default, document identifiers are ObjectId values in MongoDb. A unique index is created for the _id field in every collection. It would be advantageous if MongoDb could use this index (partially) to effectively filter and sort documents by their creation date stored in the ObjectId values.

However, I clould not find information about this anywhere. I know for a fact that MongoDb can use compound (multi-field) indexes partially to speed up filter/sort operations (MongoDb use indexes partially) But this question is somewhat different. The primary key's unique index is not a compound (multi-field) index. But it shares some properties with multi-field indexes. In particular, the order of ObjectId values and the order of creation dates are the same. So in theory, it would be possible to use it as a partial index. I just don't know if MongoDb can do that or not. Of course, it also depends on the index type. If the primary key's index is implemented with a hash, then it probably cannot be used for sorting and filtering, only for lookups.

Can anybody tell, if I need to create a separate "created" datetime field and an index on it, to be able to effectively sort and order documents by the creation date? (I'm using mongodb 4.2)

  • The ObjectId's datetime value has a precision of seconds only. E.g., the value ObjectId("5eaab2df1347cc3a123a2878") has a datetime of ISODate("2020-04-30T11:13:35Z") (this can be got from ObjectId#getTimestamp() method). – prasad_ Apr 30 at 13:49
  • I'd be more than happy with one second precision. – nagylzs Apr 30 at 18:49
  • That also means the created datetime is not unique. – prasad_ May 1 at 2:51
  • Yes, it means that. I need this to store forum messages in a collection, and retrieve them for given intervals. One second precision is more than enough for displaying forum messages, and the timestamp is indeed no unique. In this particular application, it is not a problem at all. – nagylzs May 1 at 13:59
  • As far as your question goes: are you planning to filter on other fields or are you just looking to sort by _id? If you need to filter and sort by different fields, you should create an appropriate compound index. MongoDB will use the same index for filtering & sorting. See: Use Indexes to Sort Query Results. – Stennie May 5 at 7:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.