How do I make Perl Mongo find() return the records where ts is less than 5 days old?

I have a MongoDB collection called Clickstream and want to find all documents matching a specific pubCode with a timestamp in the last 5 days. The timestamp is in a field called ts, with a Mongo data type of Date.

From the Mongo shell I can execute the following:-

    db.Clickstream.find({
        pubCode : "w_abc123",
        ts: {$gte: ISODate("2014-02-28T00:00:00Z")}
    })

However, I can't seem to do the same thing in Perl (using today's date, rather than a hardcoded date & time). If I do this:-

    my $now = DateTime->now;
    my $n_days_ago = $now->add( days => -5 );
    ... etc ...
    my $ptr = $ptrClickstream->find(
        {
            pubCode => "$pubCode",
            ts => { "\$gte" => ISODate("$n_days_ago") }
        }, 
        { hitType => 1 }
    );

I get the following error message:-

Undefined subroutine &main::ISODate called at recalcPubPop.pm line 25.

And if I remove the reference to ISOData() like this

    my $ptr = $ptrClickstream->find({
        pubCode => "$pubCode", ts => { "\$gte" => "$n_days_ago" }
    });

I get no documents returned.

Any suggestions on how to make this find() return the records where ts is less than 5 days old? Thanks!

Solution

Use DateTime naturally. Just explaining for entirety.

So where you see that ISODate form from within the mongo shell, that is actually just a native representation for the shell's JavaScript environment of the underlying BSON Date that is really being stored.

As such, the Perl driver "inflates" these values as DateTime objects when you retrieve them, and in a similar manner handles the conversion to the BSON Date when you pass in the values.

So you already did the DateTime math part in your code:

my $now = DateTime->now;
my $n_days_ago = $now->add( days => -5 );

For reference ->add works "in-place" and you seem to want the "start" of the day so we can truncate.

Try and force "UTC" dates, which is what they will be in the collection That is just for completeness as they generally should be that way. You may find this form of coding your date a bit cleaner:

my $then = DateTime->now( time_zone => 'UTC' )
             ->truncate( to => 'day' )->add( days => -5 );

And your second query form was generally right, but your real problem was you we're coercing to string by interpolating as in "$n_days_ago", so you didn't have the object but the string. See the form as I have amended and don't try to interpolate unless you really mean to:

my $ptr = $ptrClickstream->find({
    pubCode => $pubCode, ts => { '$gte' => $then } 
});

So all of this works for me. If that does not return a result check your vars that their values are definitely what you expect.

All of the driver implementations handle native "Date Objects" for their language in this way.

For more information, see the driver section on Dates.