mongodbmongodb-queryaggregation-frameworkobjectidmongodb-indexes

How to query collection for documents created at a certain time range using the ObjectId field?

I have a collection that has the default _id field, which stores the ObjecId which contains the timestamp of creation.

How do I write a query to find all docs created between MIN_DATE and MAX_DATE?

EDITED:

It must still be able to use the index.

Solution

  • You can compare ObjectId values using $lt and $gt. Since we know that the first part of the ObjectId is the epoch timestamp, we can construct an ObjectId value that correspond to a specific time. In mongosh that might look like:

    > ObjectId((new Date("2022-03-01T00:00+0000").valueOf()/1000).toString(16) + "0000000000000000")

ObjectId("621d62000000000000000000")

    That constructed value would be greater than any ObjectId created prior to the specified datetime (1 Mar 2022 midnight UTC in this case), and less than or equal to any ObjectId created during that second or after.

    Once you contruct an ObjectId values for both dates of interest, query the collection similar to:

    db.collection.find({_id:{$gte:olderObjectIdValue, $lt:newerObjectIdValue}})