69

I'm using the Mongo shell to query my Mongo db. I want to use the timestamp contained in the ObjectID as part of my query and also as a column to extract into output. I have setup Mongo to create ObjectIDs on its own.

My problem is I can not find out how to work with the ObjectID to extract its timestamp.

Here are the queries I am trying to get working. The 'createdDate' field is a placeholder; not sure what the correct field is:

//Find everything created since 1/1/2011
db.myCollection.find({date: {$gt: new Date(2011,1,1)}});

//Find everything and return their createdDates
db.myCollection.find({},{createdDate:1});
Alex Bitek
  • 6,529
  • 5
  • 47
  • 77
emilebaizel
  • 4,505
  • 5
  • 27
  • 20

4 Answers4

108

getTimestamp()

The function you need is this one, it's included for you already in the shell:

ObjectId.prototype.getTimestamp = function() {
    return new Date(parseInt(this.toString().slice(0,8), 16)*1000);
}

References

Check out this section from the docs:

This unit test also demostrates the same:

Example using the Mongo shell:

> db.col.insert( { name: "Foo" } );
> var doc = db.col.findOne( { name: "Foo" } );
> var timestamp = doc._id.getTimestamp();

> print(timestamp);
Wed Sep 07 2011 18:37:37 GMT+1000 (AUS Eastern Standard Time)

> printjson(timestamp);
ISODate("2011-09-07T08:37:37Z")
Community
  • 1
  • 1
Chris Fulstow
  • 41,170
  • 10
  • 86
  • 110
  • 3
    Do you have an example of how to call this function from the shell? I tried something like _id.getTimestamp() but Mongo isn't loving that. Thanks. – emilebaizel Sep 07 '11 at 06:50
  • 8
    This is helpful for after I've retrieved the data, but I still don't see how I would use the created date as a query parameter. E.g. 'give me all widgets that were created after 1/1/2011'. Maybe I'm missing something? – emilebaizel Sep 07 '11 at 16:04
  • FYI- you can also do it in one line: > ObjectId().getTimestamp() ISODate("2011-09-07T16:17:10Z") – mstearn Sep 07 '11 at 16:17
  • 1
    It turns out you can't query against the date in the ObjectId. So I'm going to add my own timestamp column. Would have been nice to use the built in timestamp in the ObjectId. Thanks for all the help around querying it on the result sets! – emilebaizel Sep 13 '11 at 21:27
  • You can probably do this: `db.col.find( { $where: "this._id.getTimestamp() > new Date(2011,0,1)" } );` but it'll be slow over large data sets because it executes the JavaScript on every document. I'd go with a separate column. – Chris Fulstow Sep 13 '11 at 23:27
  • All this gives me is AttributeError: 'ObjectId' object has no attribute 'getTimeStamp' – zakdances Aug 14 '12 at 22:42
  • 1
    @yourfriendzak The method is called getTimestamp() http://docs.mongodb.org/manual/reference/method/ObjectId.getTimestamp/#ObjectId.getTimestamp – Alex Bitek Jan 03 '13 at 13:24
  • @emilebaizel You can use the timestamp in a query, but first you need to convert the 10 digits long UNIX timestamp to a hexadecimal string of **8 characters** then append 0000000000000000 to it (in the end the string should have exactly **24 characters**). You then take the resulting string and use it as an object id in MongoDB queries. See my comment here: http://goo.gl/XJDa1 for an example. – Alex Bitek Jan 03 '13 at 13:45
  • I created a simple webpage to extract the timestamp from a given Mongo ObjectId: http://paumoreno.net/mongoid/ I Hope it can be useful! – pau.moreno Dec 21 '13 at 23:11
  • If you need to display only year or month, etc. you can use `print(timestamp.getFullYear())` or `print(timestamp.getMonth() + 1)` – boryn Apr 01 '14 at 11:07
  • I would change `this.toString()` to `this.str`. It was not working for me because of that. The resulting function would be: `ObjectId.prototype.getTimestamp = function() { return new Date(parseInt(this.str.slice(0,8), 16)*1000); }` – Jose Enrique Nov 05 '16 at 17:26
  • mongodb docs domain changed from .org to .com – Phani Rithvij Mar 09 '21 at 05:34
20

This question is helpful to understand of how to use the _id's embedded timestamp in query situations (refers to the Mongo Extended JSON documentation). This is how it's done:

col.find({..., 
     '_id' : {'$lt' : {'$oid' : '50314b8e9bcf000000000000'}} 
})

finds documents created earlier than the one that's given by oid. Used together with natural sorting and limiting you can utilize BSON _ids to create Twitter-like API queries (give me the last OID you have and I'll provide twenty more)

Community
  • 1
  • 1
Stefan
  • 3,382
  • 4
  • 23
  • 27
  • 1
    Your answer can actually be used inside a mongo query. Thanks ! – Bertie Dec 13 '12 at 10:56
  • This only works using *mongoexport* command: mongoexport -d twitter -c tweets -q '{"_id" : {"$gte" : {"$oid" : "50e54ec00000000000000000"}}}' I can't get any results back using the *mongo shell*: db.tweets.find({"_id" : {"$gte" : {"$oid" : "50e54ec00000000000000000"}}}) – Alex Bitek Jan 03 '13 at 13:05
  • 2
    Managed to get it working in the Mongo shell as well: `db.tweets.find({ "_id" : { $gte : ObjectId("50d314e40000000000000000") } })` and using the C++ MongoDB driver with the following query: `std::string qs = "{ \"_id\" : { $gte : { \"$oid\" : \"" + oid + "\" } } }"; std::auto_ptr cursor = c.query("twitter.tweets", mongo::Query(qs));` – Alex Bitek Jan 03 '13 at 13:32
5

In python you can do this:

>>> from bson.objectid import ObjectId
>>> gen_time = datetime.datetime(2010, 1, 1)
>>> dummy_id = ObjectId.from_datetime(gen_time)
>>> result = collection.find({"_id": {"$lt": dummy_id}})

I think, ObjectId.from_datetime() - its a useful method of standard bson lib Maybe other language bindings have alternative builtin function. Source: http://api.mongodb.org/python/current/api/bson/objectid.html

es2
  • 51
  • 1
  • 2
  • Or, put another way: Good answers accompany code samples with an explanation for future readers. While the person asking this question may understand your answer, explaining how you arrived at it could help countless others. – Stonz2 Oct 09 '14 at 19:46
  • sorry, i think i missed by topic and answered to another question – es2 Oct 09 '14 at 20:00
1

To use the timestamp contained in the ObjectId and return documents created after a certain date, you can use $where with a function.

e.g.

db.yourcollection.find( { 
  $where: function() { 
    return this._id.getTimestamp() > new Date("2020-10-01")
  } 
});

The function needs to return a truthy value for that document to be included in the results. Reference: $where

Mongo date objects can seem a bit peculiar though. See the mongo Date() documentation for constructor details.

excerpt:

You can specify a particular date by passing an ISO-8601 date string with a year within the inclusive range 0 through 9999 to the new Date() constructor or the ISODate() function. These functions accept the following formats:

    new Date("<YYYY-mm-dd>") returns the ISODate with the specified date.
    new Date("<YYYY-mm-ddTHH:MM:ss>") specifies the datetime in the client’s local timezone and returns the ISODate with the specified datetime in UTC.
    new Date("<YYYY-mm-ddTHH:MM:ssZ>") specifies the datetime in UTC and returns the ISODate with the specified datetime in UTC.
    new Date(<integer>) specifies the datetime as milliseconds since the Unix epoch (Jan 1, 1970), and returns the resulting ISODate instance.
user1160006
  • 533
  • 4
  • 12