use _id be to a date query condition

Question

I knew the _id field can be converted into date.
here is the function:

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

Then I can use doc._id.getTimestamp(); to show the document's created date time.

The thing is, the document only has the_id field that is related to date. My question is: How can I query documents of a specific date range only by _id?

Blakes Seven · Answer 1 · 2015-07-09T06:33:48.730

3

You can use $where which sends JavaScript to the server in order to query. But forget the prototype, since all logic must be self contained or otherwise loaded to the server. This means it must be evaluated in the function:

db.collection.find(function() {
    return 
        ( new Date(parseInt(this._id.toString().slice(0,8), 16)*1000)
           > new Date("2015-01-01") ) &&
        ( new Date(parseInt(this._id.toString().slice(0,8), 16)*1000)
           < new Date("2015-02-01") );
})

But it's not a great idea since $where cannot use an "index" to filter out matches.

You are better of creating a "timestamp" field with an actual date object and using the $lt and $gt operators itself. That actual field can be indexed and the operators will use that to make a selection.

An alternate way is to work out what the ObjectId values are outside of your query and then use the standard operators:

var start =  ObjectId(
    (new Date("2015-01-01").valueOf() / 1000).toString(16) + "0000000000000000")
 ),
    end =  ObjectId(
     (new Date("2015-02-01").valueOf() / 1000).toString(16) + "0000000000000000")
 );

db.collection.find({ "_id": { "$gt": start, "$lt": end } })

That creates ObjectId values from the dates and allows you to use the primary key index that is always there.

Some drivers even support a "create from timestamp" method on their ObjectId implementation.

edited Jul 09 '15 at 06:33

answered Jul 09 '15 at 06:23

Blakes Seven

49,422
14
129
135

@crazytomcat Did you read the comments on the answer you accepted? You do realize that author "copied" the content from this answer after I had posted it and that their original answer could not possibly work. You need to read that. Accepting their answer is just wrong. – Blakes Seven Jul 09 '15 at 12:00
I tried _Dallas Lones_' answer and works well. But I don't know who copied whose answer. Sorry about that. Anyway, I really appreciate your help. – crazytomcat Jul 09 '15 at 12:16
@crazytomcat If you read the comments carefully it is very clear. Also you should be able to review the revision history which is well recorded and shows the original response. Edits on stackoverflow are not recorded within 5 minute intervals. So each edit must have taken place 5 or more minutes apart. That copied answer shows 5 edits so 25 minutes at least can be accounted for since my initial comment there. As also mentioned the edited times are only displayed after 1 hour as a hour. It should be clear the answer is copied. Please do the right thing and do not reward that kind of behaviour. – Blakes Seven Jul 09 '15 at 12:22
@crazytomcat Also "robertklep" comments "moving my upvote to your answer" but of course he cannot do that within several hours since stackoverflow will not allow that unless the answer he upvoted is edited. So his upvote ( and the only one ) still remains there at present. – Blakes Seven Jul 09 '15 at 12:24
@crazytomcat You really seem to need convinving. Here is my own answer on another question a few weeks ago when I first signed up which uses the same logic. If I am the person who copied the answer then why did I post this weeks before: http://stackoverflow.com/questions/2824157/random-record-from-mongodb/31071607#31071607 – Blakes Seven Jul 09 '15 at 12:44

Dallas Lones · Accepted Answer · 2015-07-09T06:35:42.300

0

function GetTimeID(yourTime){
    yourTime = new Date(yourTime);
    yourTime = Math.floor(yourTime/1000).toString(16);
    var newID = ObjectId(yourTime + "0000000000000000");
    return newID;
}

var firstDateID = GetTimeID("1/1/2015");
var secondDateID = GetTimeID("1/1/2015");

var batList = batmanCollection.find({_id: {$gt: firstDateID }}).toArray();

or

var batList = batmanCollection.find({_id: {$lt: secondDateID }}).toArray();

or

var batList = batmanCollection
    .find({_id: {$lt: firstDateID , $gt: secondDateID }}).toArray();

edited Jul 09 '15 at 06:35

answered Jul 09 '15 at 06:16

Dallas Lones

66
6

You cannot do that. `_id` is not a timestamp or date value in the question. It's an `ObjectId` – Blakes Seven Jul 09 '15 at 06:24
@BlakesSeven you _can_ do that, as `ObjectId`'s encode a timestamp in their value. – robertklep Jul 09 '15 at 07:38
@robertklep The answer has been changed, and "after" I wrote the same process it was revived to. As people should be able to see in the revisions. Not to mention of course that the `Date()` constructors here are wrong and will not produce a UTC date, but will produce a local date instead. – Blakes Seven Jul 09 '15 at 07:47
@BlakesSeven fair enough, although the revisions are a bit unclear on the order in which the answers were updated (yours was updated as well). – robertklep Jul 09 '15 at 07:49
@robertklep Well yes. Unfortunately you only see in "hours" after a certain point. If in doubt always look at the initial state and you can clearly see how "off the rails" this was. – Blakes Seven Jul 09 '15 at 07:50
i suppose that's what I get for trying to help you by pointing out what you were doing wrong here. Plagarising and answer is so very not cool. – Blakes Seven Jul 09 '15 at 08:05
Highly rude. This was essentially your content of your post at the time of my comment which was after the complete revison by myself `batmanCollection.find({_id: {$gt: yourTimestamp}}).toArray()` with absolutely none of the copied code. And you posted that 10 minutes before I commented. – Blakes Seven Jul 09 '15 at 12:05

use _id be to a date query condition

2 Answers2