mysql BETWEEN equivalent in MongoDB for date range search

[Mohamad A]

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.

[Dwight Merriman]

i don't really know php or the php driver, but perhpaps you need new MongoDate() in your latter condition statement example, so that the data types line up.

here is a simple example from the mongo shell.  you might try it there first, then in php : 

> db.foo.insert({x:17})

> db.foo.find( { x : { $gte : 10, $lte : 30 } } )

{ "_id" : ObjectId("53baee5213aca84d271e4d8b"), "x" : 17 }

[Asya Kamsky]

Just as a clarification, when you say you want events from 5th of July to 12th of July do you want events that had any overlap with this date range?  So July 1st to 20th should be returned as well as July 6th-8th?

If so then your condition is not correct - you want to specify start_time < end_date AND end_time > start_date.   If event started before your end date and it ended after your start date then it must overlap your date range.

Asya

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/a5270842-cb3b-4ad8-ab6f-de3070df2f62%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.