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

Question

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});

Solution 1

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:

• Extract insertion times from _id rather than having a separate timestamp field

This unit test also demostrates the same:

• mongo / jstests / objid6.js

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

Solution 2

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)

Solution 3

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

Solution 4

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.

View more solutions

Comments

[emilebaizel]

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]

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?

[mstearn]

FYI- you can also do it in one line: > ObjectId().getTimestamp() ISODate("2011-09-07T16:17:10Z")

[emilebaizel]

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!

[Chris Fulstow]

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.

[zakdances]

All this gives me is AttributeError: 'ObjectId' object has no attribute 'getTimeStamp'

[Albert Gan]

Your answer can actually be used inside a mongo query. Thanks !

[Alex Bitek]

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]

@yourfriendzak The method is called getTimestamp() docs.mongodb.org/manual/reference/method/ObjectId.getTimesta‌​mp/…

[Alex Bitek]

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<mongo::DBClientCursor> cursor = c.query("twitter.tweets", mongo::Query(qs));

[Alex Bitek]

@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: goo.gl/XJDa1 for an example.

[pau.moreno]

I created a simple webpage to extract the timestamp from a given Mongo ObjectId: paumoreno.net/mongoid I Hope it can be useful!

[boryn]

If you need to display only year or month, etc. you can use print(timestamp.getFullYear()) or print(timestamp.getMonth() + 1)

[Stonz2]

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.

[es2]

sorry, i think i missed by topic and answered to another question

[Jose Enrique]

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); }

[Phani Rithvij]

mongodb docs domain changed from .org to .com