CODEWITHSUNDEEP
mongodb
codepiper
codepiper

Reputation: 109

Mongo - find with dates not working

> db.samplecollection.find();
{
    "_id" : ObjectId("53c5ff4339c4473a278b46ca"),
    "start_time_mongo" : ISODate("2014-08-25T05:30:00Z"),
    "end_time_mongo" : ISODate("2014-08-25T06:30:00Z"),
}

Above collection has one document.

I want to search this collection with for some date range like below 2 examples.

Query1 : dates given between 17th june to 18th august - with javascript date function

Answer : Result shows document which is not in date range

> db.samplecollection.find({ $or: [ { start_time_mongo: { $lte: new Date("1407954599000") } }, { end_time_mongo: { $gte: new Date("1403029800000") } } ]},{start_time_mongo:1,end_time_mongo:1}).pretty();
{
    "_id" : ObjectId("53c5ff4339c4473a278b46ca"),
    "start_time_mongo" : ISODate("2014-08-25T05:30:00Z"),
    "end_time_mongo" : ISODate("2014-08-25T06:30:00Z")
}

Query2 : dates given between 17th june to 18th august - with javascript date function

Answer : Result shows document which is not in date range

> db.samplecollection.find({ $or: [ { start_time_mongo: { $lte: ISODate("2014-08-13T18:29:59Z") } }, { end_time_mongo: { $gte: ISODate("2014-06-17T18:30:00Z") } } ] }, {start_time_mongo:1,end_time_mongo:1}).pretty();
{
    "_id" : ObjectId("53c5ff4339c4473a278b46ca"),
    "start_time_mongo" : ISODate("2014-08-25T05:30:00Z"),
    "end_time_mongo" : ISODate("2014-08-25T06:30:00Z")
}
>

Upvotes: 0

Views: 176

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151220

Your usage of $or is not correct here. You seem to be trying to do a "range" query to find dates "between" those dates. So here you need:

db.samplecollection.find(
    { 
        start_time_mongo: { $lte: new ISODate("2014-08-13T18:29:59Z") },
        end_time_mongo: { $gte: new ISODate("2014-06-17T18:30:00Z") } }
    }
    {start_time_mongo:1,end_time_mongo:1}
).pretty()

That is a logical "and" which says both of those date condtions need to be met and only returns documents that would meet both conditions. You would also presumably generally do that the other way around, so that dates were "between" the values:

db.samplecollection.find(
    { 
        start_time_mongo: { $gte: new ISODate("2014-06-17T18:30:00Z") } },
        end_time_mongo: { $lte: new ISODate("2014-08-13T18:29:59Z") }
    }
    {start_time_mongo:1,end_time_mongo:1}
).pretty()

The query you issued:

 db.sample.find(
     { 
         $or: [ 
             { start_time_mongo: { $lte: new ISODate("2014-08-13T18:29:59Z") } },
             { end_time_mongo: { $gte: new ISODate("2014-06-17T18:30:00Z") } } 
         ]
     }, 
     {start_time_mongo:1,end_time_mongo:1}
 ).pretty()

Is correct in what it is evaluating here as the dates are greater than the second condition and that evaluates to true. That is what $or does, and returns when either argument is true.

Upvotes: 1

Related Questions