CODEWITHSUNDEEP
phpmysqlmongodb
codepiper
codepiper

Reputation: 109

mysql BETWEEN equivalent in MongoDB

In PHP & MongoDB, i am trying to read all instances of events for a specific calendar and a specific period. Each event has start and end date. They are of MongoDate type in mongodb.

I use below condition to get the events from mongo.

$condition = array(
 'start_time'    => array('$gte'    =>  new MongoDate($start_day)), 
 'end_time'      => array('$lte'    => new MongoDate($end_day))
);

Here is my problem, i am missing the events whose start date is to my date range.

For example event starts on 01-july-2014
event ends on 10-july-2014

my mongo query - give me events from 5-july to 12-july 

$condition = array(
  'start_time'    =>    array('$gte'    => '5-july-2014'), 
  'end_time'      =>    array('$lte'    => '12-july-2014')
);

When i run above mentioned event is not returned.

Any suggestions are welcome?

In mysql BETWEEN helps me to get the required results.

Upvotes: 0

Views: 819

Answers (2)

Philipp
Philipp

Reputation: 69683

Your query selects any events which are completely contained in the given date range, but when I understood you correctly you want events which partly or completely overlap the date-range.

A date-range overlaps another date-range when the start of A is before the end of B and the end of A is after the start of B. So try

'event_start'    => array('$lte'    =>  new MongoDate($search_end)), 
'event_end'      => array('$gte'    => new MongoDate($search_start))

Another pitfall I should mention is that a MongoDate doesn't represent a single day, but an exact point in time. When you omit the time, you get midnight at that date. So '$lte' => '12-july-2014' gives you 2014-04-12 0:00:00.000 which will not select anything on july 12th. When you want to include that day, either manually set a time like 12-july-2014 23:59:59 or just use the next day.

Upvotes: 3

Neil Lunn
Neil Lunn

Reputation: 151132

No direct equivalent of "between", but with data like this:

{
    "start_time" : ISODate("2014-07-01T00:00:00Z"),
    "end_time" : ISODate("2014-07-10T00:00:00Z")
}
{
    "start_time" : ISODate("2014-07-05T00:00:00Z"),
    "end_time" : ISODate("2014-07-12T00:00:00Z")
}
{
    "start_time" : ISODate("2014-07-07T00:00:00Z"),
    "end_time" : ISODate("2014-07-14T00:00:00Z")
}
{
    "start_time" : ISODate("2014-07-13T00:00:00Z"),
    "end_time" : ISODate("2014-07-14T00:00:00Z")
}

This works:

db.collection.find(
    {
        "$or": [
            { "start_time": { 
                "$gte": new Date("2014-07-05"),
                "$lte": new Date("2014-07-12")
              }
            },
            { "end_time":   { 
                "$gte": new Date("2014-07-05"),
                "$lte": new Date("2014-07-12")
              } 
            }
        ]
    }
)

And produces:

{
    "start_time" : ISODate("2014-07-01T00:00:00Z"),
    "end_time" : ISODate("2014-07-10T00:00:00Z")
}
{
    "start_time" : ISODate("2014-07-05T00:00:00Z"),
    "end_time" : ISODate("2014-07-12T00:00:00Z")
}
{
    "start_time" : ISODate("2014-07-07T00:00:00Z"),
    "end_time" : ISODate("2014-07-14T00:00:00Z")
}

Which is just the items that would have dates within the range "between" start and end.

Upvotes: 1

Related Questions