How do I extract the created date out of a Mongo ObjectID

66
votes

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});
4
shellmongodbtimestampunix-timestamp

4 Answers

104
votes

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")
20
votes

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)

5
votes

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

1
votes

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.