Querying for last 7 or 30 days date range with mongodb mongoid rails

Question

I am using Mongodb 2.4.8, Mongoid 4.0.2 and Rails 4.2.1

I have been trying to get a date range query ran through mongoid to return the right set of results on a test data with mongodb. I created just 6 documents spanning over 5 - 7 days period. For instance, the query below, ought to return 3 records for the days between 22nd - 27th, instead it returns one record :

Person.where(:person_email_clicks.elem_match => {:date.gte =>  'Wed, 22 Apr 2015 22:12:00 +0000'}, :person_email_clicks.elem_match => {:date.lte => 'Mon, 27 Apr 2015 22:12:00 +0000'})

This is the entire documents for test purposes:

{"_id"=>BSON::ObjectId('55364a416461760bf1000000'),
   "status"=>"premium"
   "person_email_clicks"=>[
      {"_id"=>BSON::ObjectId('55381d256461760b6c000000'), "name"=>"buyer", "date"=>2015-04-22 22:12:00 UTC},

      {"_id"=>BSON::ObjectId('55381d536461760b6c010000'), "name"=>"seller", "date"=>2015-04-23 01:12:00 UTC}, 

      {"_id"=>BSON::ObjectId('55381d916461760b6c020000'), "name"=>"giver", "date"=>2015-04-24 22:12:00 UTC}, 

      {"_id"=>BSON::ObjectId('55381dac6461760b6c030000'), "name"=>"help", "date"=>2015-04-27 22:12:00 UTC}, 

      {"_id"=>BSON::ObjectId('553824ba6461760b6c040000'), "name"=>"tt", "date"=>2015-04-22 22:12:00 UTC}, 

      {"_id"=>BSON::ObjectId('553824bf6461760b6c050000'), "name"=>"tt", "date"=>2015-04-22 22:12:00 UTC}, 

      {"_id"=>BSON::ObjectId('55382ad46461760b6c060000'), "name"=>"yyy", "date"=>2015-04-25 22:12:00 UTC}
    ]
}

Here are the models:

class Person
   include Mongoid::Document
   field :status,  type: String
   embeds_many :person_email_clicks
end

class PersonEmailClick
    include Mongoid::Document

    field :name, type: String
    field :date, type: DateTime 
    embedded_in :person
end

These are the queries, I have tried so far and they all return 1 record instead of 3 records between the dates 22nd and 27th

 u = Person.first.person_email_clicks.first.date
 We store the datetime which is Wed, 22 Apr 2015 22:12:00 +0000

 e = Person.first.person_email_clicks.last.date
 We store the the 2nd date which is Mon, 27 Apr 2015 22:12:00 +0000

Now I use those variables holding the dates in my query and all 3 query below returns one record instead of 3:

 f = Person.where(:person_email_clicks.elem_match => {:date.gte =>  u}, :person_email_clicks.elem_match => {:date.lte => e})

 f = Person.where(:person_email_clicks.elem_match => {:date => {"lte" =>  u}}, :person_email_clicks.elem_match => {:date => {"gte" => e}})

 t = Person.where('person_email_clicks.date' => u..e)

Any suggestions on how to tweak this to return the 3 records between the date range of 22nd - 27th ?

your issue might not be in the correctness of the query... [check this issue about mongoid querying dates in github](https://github.com/mongoid/mongoid/issues/3590) — a14m, Apr 23 '15 at 18:38
try to query using the following (which worked for me) `DateTime.parse('Wed, 22 Apr 2015 22:12:00 +0000').in_time_zone('UTC')` other date formats didn't work (as shown in the issue)... but make sure to use the correct timezone — a14m, Apr 23 '15 at 19:43
Thanks. I just tried it for a range query and it still returned 1 record. This is the query I ran **Person.where(:person_email_clicks.elem_match => {:date.gte => DateTime.parse('Wed, 22 Apr 2015 22:12:00 +0000').in_time_zone('UTC')}, :person_email_clicks.elem_match => {:date.lte => DateTime.parse(' Mon, 27 Apr 2015 22:12:00 +0000').in_time_zone('UTC')})** — brg, Apr 23 '15 at 20:10
ok one last thing to make sure that you are not missing some records because of the seconds value... try it as follows `Person.where(:person_email_clicks.elem_match => {:date.gte => DateTime.parse('Wed, 22 Apr 2015 22:12:00 +0000').in_time_zone('UTC').beginning_of_day}, :person_email_clicks.elem_match => {:date.lte => DateTime.parse(' Mon, 27 Apr 2015 22:12:00 +0000').in_time_zone('UTC').end_of_day})` notice the **beginning_of_day** and the **end_of_day** — a14m, Apr 23 '15 at 20:13
I noticed also that you are using the elem_match twice... which i think is wrong.. you need it only once... as follows `Person.where(:person_email_clicks.elem_match => {:date.gte => DateTime.parse('Wed, 22 Apr 2015 22:12:00 +0000').in_time_zone('UTC').beginning_of_day, :date.lte => DateTime.parse(' Mon, 27 Apr 2015 22:12:00 +0000').in_time_zone('UTC').end_of_day})` — a14m, Apr 23 '15 at 20:21
I just ran the query you provided and it still returned 1 record. Do you know of an alternative approach of doing date range queries . I am beginning to think of storing perhaps only days as integer seconds. I will try more datetime query tonight and if it still fails, do you think storing integer as seconds and doing a range query with it to return documents is a good idea? — brg, Apr 23 '15 at 20:21
Just tried it now and returned 1 record. I used: **i = Person.where(:person_email_clicks.elem_match => {:date.gte => DateTime.parse('Wed, 22 Apr 2015 22:12:00 +0000').in_time_zone('UTC').beginning_of_day, :date.lte => DateTime.parse(' Mon, 27 Apr 2015 22:12:00 +0000').in_time_zone('UTC').end_of_day})** — brg, Apr 23 '15 at 20:30
it might be helpful to document these in the question... and add which record it returns (the first or last) so that others don't have to go through these comments — a14m, Apr 23 '15 at 20:32
@artmees thanks, I have finally resolved it. I needed to stored the date in a variable as before and then query it this way: **Person.first.person_email_clicks.gt(date: u).lt(date: e)**. Where **e** is _Sat, 25 Apr 2015 22:12:00 +0000_ and **u** is _Wed, 22 Apr 2015 22:12:00 +0000_. This returned _2 records _which is correct. I repeated it **e** as _Mon, 27 Apr 2015 22:12:00 +0000_ which returned _3 records_ which is correct again. Thanks for your time and help. cheers — brg, Apr 23 '15 at 21:13
great.. post it as an answer so that the community could make use of it — a14m, Apr 23 '15 at 21:15

brg · Accepted Answer · 2015-04-23T21:42:14.427

This has finally been resolved. I needed to stored the date in a variable and then query it this way:

Person.first.person_email_clicks.gt(date: u).lt(date: e).

or query it this way:

Person.first.person_email_clicks.where({:date.gt =>  u, :date.lt  =>  e})

This is the full step to get the desired results:

u = Person.first.person_email_clicks.first.date
   => Wed, 22 Apr 2015 22:12:00 +0000

e = Person.first.person_email_clicks.last.date
   => Sat, 25 Apr 2015 22:12:00 +0000

h = Person.first.person_email_clicks.gt(date: u).lt(date: e).to_a

Which returned 2 records as shown below:

 => [
      #<PersonEmailClick _id: 55381d536461760b6c010000, name: "seller", date: 2015-04-23 01:12:00 UTC, daily_total: 0, email_open: 0, page_views: 0, clicks: 0, visits: 0>, 

      #<PersonEmailClick _id: 55381d916461760b6c020000, name: "giver", date: 2015-04-24 22:12:00 UTC, daily_total: 0, email_open: 0, page_views: 0, clicks: 0, visits: 0>
 ]

Querying for last 7 or 30 days date range with mongodb mongoid rails

1 Answers1