176

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

2

13 Answers 13

250

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') } });
11
  • 33
    Very handy .. FYI, you can save this function in your ~/.mongorc.js file to have it available when the mongo shell starts up.
    – Stennie
    Nov 27, 2012 at 23:14
  • 1
    I get ReferenceError: ObjectId is not defined. How do i fix that?
    – peter
    Mar 18, 2013 at 6:17
  • 3
    I am using nodejs with mongodbnative. Fixed the "not defined error" by including var ObjectId = require('mongodb').ObjectID;
    – peter
    Mar 18, 2013 at 6:35
  • 1
    If you are using Mongoskin like I do: Change ObjectId(hexSeconds + "0000000000000000"); to db.ObjectID.createFromHexString(hexSeconds + "0000000000000000"); Dec 3, 2013 at 9:08
  • 2
    Or, in Mongoose, replace ObjectId() with: require('mongoose').Types.ObjectId() - where require('mongoose') is your initialized/configured Mongoose instance.
    – toblerpwn
    Jun 11, 2014 at 1:19
48

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
  • 1
    Note using datetime.datetime.utcnow() or datetime.datetime.today() will return the same result. The datetime is handled for you.
    – radtek
    Sep 22, 2014 at 15:05
  • Alternatively, without using pymongo dependency: epoch_time_hex = format(int(time.time()), 'x') (don't forget to append zeroes for your query) The time package was used (import time). Oct 13, 2016 at 15:46
  • 1
    Realize the OP was asking for javascript, but this really helped me simplify my code. Thanks.
    – Fred S
    Apr 9, 2019 at 18:36
43

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

1
  • 3
    This is the best/easiest way to create a ObjectId from a timestamp in a javascript env. Which is what the op asks for... Jan 16, 2015 at 14:33
33

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")]}
    ]
  }
})
1
  • Just wanted to ask as using $convert OR $toDate, Mongo will first have to convert and then compare whether doc is present in range or not, but If I would use accepted answer's approach then converting date to a ObjectId I would only have to do on client side and only once, So don't you think that solution will be more efficient than this? thanks anyway for telling that these two operators also exist :) Jan 1, 2019 at 14:56
19

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")
  }
})
16

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
2
  • 1
    I was hoping for something that would actually allow to do things like get a count of objects created before a certain time using the time embedded in the ObjectId, but it seems like that'd don't directly accessible. Thanks.
    – Zach
    Jan 6, 2012 at 16:11
  • 1
    You can do that, look at Leftium's answer.
    – rxmnnxfpvg
    Jan 6, 2012 at 19:29
14

Yes you can query object by date using MongoDB inserted ID

db.collectionname.find({_id: {$lt: ObjectId.fromDate( new ISODate("TZformat") ) } });

let's suppose users is my collection and I want all users created less than 05 January 2018

db.users.find({_id: {$lt: ObjectId.fromDate( new ISODate("2018-01-05T00:00:00.000Z") ) } });

For running from a query we can use like

db.users.find({_id: {$lt: ObjectId.fromDate(new Date((new Date().getTime() - (1 * 3 * 60 * 60 * 1000))) ) } })

All the users from the current time - 3 hours

7

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" )}})
2
  • 1
    Use $gt instead of $lt. Otherwise, it finds documents inserted before (today-60 days).
    – jschildgen
    Jun 14, 2016 at 12:47
  • 1
    @Vivek The first 4 bytes of ObjectId represent the number of seconds since the unix epoch (1970/1/1 00:00:00 UTC), and as such you can use it with greater than ($gt) and less than ($lt) to find objects that were created within a certain window. Mar 29, 2018 at 3:00
5

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()
5

A Solution Filtering within MongoDB Compass.

Based on versions:

  • Compass version: 1.25.0
  • MongoDB version: 4.2.8

Option 1:

@s7vr 's answer worked perfectly for me. You can paste this into the Filter field:

{$expr: { $and: [ {$gte: [{$toDate: "$_id"}, ISODate('2021-01-01')]}, {$lt: [{$toDate: "$_id"}, ISODate('2021-02-01')]} ] } }

Option 2:

I also found this to work (remember that the Date's month parameter is 0-based indexing so January is 0):

{_id: {$gte: ObjectId(Date(2021, 0, 1) / 1000), $lt: ObjectId(Date(2021, 1, 1) / 1000) } }

Option 3:

Equivalent with ISODate:

{_id: {$gte: ObjectId(ISODate('2021-01-01') / 1000), $lt: ObjectId(Date('2021-02-01') / 1000) } }

After writing this post, I decided to run the Explain on these queries. Here's the skinny on performance:

  • Option 1: 39 ms, 0 indexes used, 30 ms in COLLSCAN
  • Option 2: 0 ms, _id index used
  • Option 3: 1 ms, _id index used, 1 ms in FETCH

Based on my rudimentary analysis, it appears that option 2 is the most efficient. I will use Option 3, personally, as it is a little cleaner to use ISODate rather than remembering 0-based month indexing in the Date object.

4

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

2

Using MongoObjectID you should also find results as given below

db.mycollection.find({ _id: { $gt: ObjectId("5217a543dd99a6d9e0f74702").getTimestamp().getTime()}});
1
  • 3
    your query statement assume one knows the ObjectId value to begin with which is not always the case. Mar 25, 2015 at 19:21
1

In rails mongoid you can query using

  time = Time.utc(2010, 1, 1)
  time_id = ObjectId.from_time(time)
  collection.find({'_id' => {'$lt' => time_id}})

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.