0

I have a model (Events) with five years worth of records (one record per day). I need a method that, when passed a date object such as 2011-12-25 00:00:00, will show me ALL the records that have happened on 12/25 (querying against the :created_at column), regardless of the year or time that's passed.

I tried the following query to retrieve data that are in between given date but i got an issue after year change my query not able to retrieve records. for example record sdate="1-06-2013" and edate ="2-2-2014" i want records of 1st January then how can i get this data.

db.events.find( "this.sdate.getMonth() > 6 && this.edate.getMonth() < 6" );

I need exact following query using in Mongoid rather than ActiveRecord

Model.where("MONTH(created_at) = ? and DAY(created_at) = ?", somedate.month, somedate.day)

santosh
  • 1,611
  • 1
  • 13
  • 21
  • You would need to use the aggregation framework to get this result as the regular query engine doesn't support extracting just the month and day of a date and doing operations on it. `where` is not recommended for performance reasons. – WiredPrairie Sep 17 '13 at 19:09
  • This Q&A gives you some of the details: http://stackoverflow.com/questions/8136652/query-mongodb-on-month-day-year-of-a-datetime – WiredPrairie Sep 17 '13 at 19:13

2 Answers2

2

Edit: (My first answer missed the fact that it should ignore year and time)

Use the aggregation pipeline as such:

project = { '$project' => { 
              'day' => { '$dayOfMonth' => '$created_at' },
              'month' => { '$month' => '$created_at' }
            }
          }
match = { '$match' => { 'day' => 25, 'month' => 12 } }

Events.collection.aggregate([project, match])

This should return you an array of hashes with the projected values of day and month as well as the matching events' _ids. You could then map them back to Events or whatever you need to do with them.

David T
  • 765
  • 6
  • 18
0

You could try to do some Javascripting query

db.mydatabase.mycollection.find({$where : 'return this.date.getMonth() == 11'})

But I would never do this. What I would do is store the month-day in a field, have a index on it, and have a faster query. Otherwise you will be scanning all dos.

Arthur Neves
  • 11,840
  • 8
  • 60
  • 73
  • I tried the following query to retrieve data that are in between given date but i got an issue after year change my query not able to retrieve records. for example record sdate="1-06-2013" and edate ="2-2-2014" i want records of 1st january then how can i get this data. db.events.find( "this.sdate.getMonth() > 6 && this.edate.getMonth() < 6" ); – santosh Sep 18 '13 at 04:57