Mongo date range query using _id in PHP

Question

Being relatively new to mongo, I read in the mongo manual about Optimizing Object IDs.

http://www.mongodb.org/display/DOCS/Optimizing+Object+IDs#OptimizingObjectIDs-Extractinsertiontimesfromidratherthanhavingaseparatetimestampfield.

Going with the recommendation about NOT creating a separate Created_On field, I decided I would later extract out the date from the _id field which is an ObjectID

Now, I have many records all with out a Created_On field.

I am attempting to query for a date range, but am unsure of the syntax:

$start = new MongoDate(strtotime("2012-03-01 00:00:00"));
$end = new MongoDate(strtotime("2012-03-15 00:00:00"));

$collection->find(array('_id' => array('$gt' => $start, '$lte' => $end)));

Always returns 0 results. Although I can query individual records and extract the date from the object.

Answer 1

In PHP, you need to do something like:

function timeToId($ts) {
    // turn it into hex
    $hexTs = dechex($ts);
    // pad it out to 8 chars
    $hexTs = str_pad($hexTs, 8, "0", STR_PAD_LEFT);
    // make an _id from it
    return new MongoId($hexTs."0000000000000000");
}

$start = strtotime("2012-03-01 00:00:00");
$end = strtotime("2012-03-15 00:00:00");

$collection->find(array('_id' => array('$gt' => timeToId($start), '$lte' => timeToId($end))));

Then you can use that to query the _id field.

I wrote a blog post describing the process here: http://www.snailinaturtleneck.com/blog/2011/12/20/querying-for-timestamps-using-objectids/