Fatih Donmez
Fatih Donmez

Reputation: 4347

MongoDB querying date

Querying with $gt is not working as expected if the date's are same. It's more like $gte.

But if I add 1 second to query param then it works.

Here is the sample query;

I have a document which it's creation_date 1367414837 timestamp.

db.collection.find({creation_date : {'$gt' : new Date(1367414837000)}});

This query matches with the document which date's 1367414837 If i increment the query timestamp just one like 1367414838. it works expected.

Im using mongo console but i have same problem in php with MongoDate

edit: output of query

db.collection.findOne({creation_date : {'$gt' : new Date(1367414837000)}});
{
    "_id" : ObjectId("5181183543c51695ce000000"),
    "action" : {
        "type" : "comment",
        "comment" : {
            "id" : 74,
            "post_id" : "174",
            "owner_id" : "5",
            "text" : "ne diyeyim lae :D",
            "creation_date" : "2013-05-01 16:27:17"
        }
    },
    "creation_date" : ISODate("2013-05-01T13:27:17.336Z"),
    "owner" : {
        "id" : "5",
        "username" : "tylerdurden"
    }
}

edit2: problem is php extension of mongo. it's documented " any precision beyond milliseconds will be lost when the document is sent to/from the database." http://php.net/manual/en/class.mongodate.php

I incremented query param one second as a turnaround solution.

Upvotes: 3

Views: 761

Answers (1)

Jim Dagg
Jim Dagg

Reputation: 2032

Dates in BSON are UNIX dates equal to milliseconds since epoch; they're accurate down to the millisecond. If the times you're inserting (and trying to match against) are accurate to the millisecond level, the element you're trying to match is possibly just a few milliseconds later than the timestamp you're querying, and $gt is likely working as expected. (2013-05-01T13:27:17.001Z is indeed later than 2013-05-01T13:27:17Z, for example.)

Upvotes: 3

Related Questions