Hi, I''m having User model & it has created_at field. If i want to find all the users who are created today. How can i write the query? created_at field has datetime in it. how can i search for records in current date? Thanks, Srikanth -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On 20 August 2010 07:17, Srikanth Jeeva <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> created_at field has datetime in it. how can i search for records in > current date?Model.all(:conditions => ["created_at BETWEEN ? AND ?", Date.today, Date.tomorrow]) -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Michael Pavling wrote:> On 20 August 2010 07:17, Srikanth Jeeva <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote: >> created_at field has datetime in it. how can i search for records in >> current date? > > Model.all(:conditions => ["created_at BETWEEN ? AND ?", Date.today, > Date.tomorrow])Thanks for the reply,, I used this , Model.find(:all, :conditions=>["DATE(created_at) = ?", Date.today]) -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Aug 19, 2010, at 11:43 PM, Srikanth Jeeva wrote:> Michael Pavling wrote: >> On 20 August 2010 07:17, Srikanth Jeeva <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote: >>> created_at field has datetime in it. how can i search for records in >>> current date? >> >> Model.all(:conditions => ["created_at BETWEEN ? AND ?", Date.today, >> Date.tomorrow]) > > Thanks for the reply,, > > I used this , > > Model.find(:all, :conditions=>["DATE(created_at) = ?", Date.today])This will be slower than the BETWEEN option above. The issue with this is that the database has to compute the date for every row in the table. Not an issue if you don''t have many rows. If you have lot of rows, it can be a noticeable performance hit. The other reason to use the BETWEEN option is if you have an index on created_at. If you do, the BETWEEN option will use it. The DATE(created_at) won''t (unless you''re index is specifically on "date(created_at)"). The below is from one of my PostgreSQL databases. press_releases.release_at has an index on it. Notice that for the first query the database opts for a "seq scan" (ie. search the entire database row by row, then filter it). The second query uses the index. I don''t have enough press releases for it to matter, but if I did, the second would be much much faster. development=# explain select * from press_releases where DATE(release_at) = ''2010-08-01''; QUERY PLAN ----------------------------------------------------------------- Seq Scan on press_releases (cost=0.00..31.63 rows=1 width=839) Filter: (date(release_at) = ''2010-08-01''::date) (2 rows) development=# explain select * from press_releases where release_at BETWEEN ''2010-08-01'' AND ''2010-08-02''; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using index_press_releases_on_release_at on press_releases (cost=0.00..8.27 rows=1 width=839) Index Cond: ((release_at >= ''2010-08-01 00:00:00''::timestamp without time zone) AND (release_at <= ''2010-08-02 00:00:00''::timestamp without time zone)) (2 rows) -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
> This will be slower than the BETWEEN option above. The issue with this > is that the database has to compute the date for every row in the table. > Not an issue if you don''t have many rows. If you have lot of rows, it > can be a noticeable performance hit. > > The other reason to use the BETWEEN option is if you have an index on > created_at. If you do, the BETWEEN option will use it. The > DATE(created_at) won''t (unless you''re index is specifically on > "date(created_at)"). >wow thanks for a brief explanation.. I will start using between.. -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
You can also use following condition for today''s date records :conditions => [''created_at between ? and ?'', date.beginning_of_day.to_s(:db), date.end_of_day.to_s(:db)] Thanks Brijesh Shah -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Apologies if this should be separated into a separate thread but this reminded me of a question I had when writing some search methods recently. What, if any, are the differences between the following two find statements? On Aug 20, 2:33 am, Michael Pavling <pavl...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 20 August 2010 07:17, Srikanth Jeeva <li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote: > > Model.all(:conditions => ["created_at BETWEEN ? AND ?", Date.today, > Date.tomorrow])-and- Model.all(:conditions => {:created_at => Date.today..Date.tomorrow}) Is the choice just a personal coding preference or is there some performance or security differences between the two? It''s my understanding that the array form''s design was to help prevent sql injection attacks but I was unsure if you lost that benefit by using the hash form with a range. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Aug 25, 3:35 pm, Bob <rpell...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Apologies if this should be separated into a separate thread but this > reminded me of a question I had when writing some search methods > recently. > > What, if any, are the differences between the following two find > statements? > > On Aug 20, 2:33 am, Michael Pavling <pavl...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > On 20 August 2010 07:17, Srikanth Jeeva <li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote: > > > Model.all(:conditions => ["created_at BETWEEN ? AND ?", Date.today, > > Date.tomorrow]) > > -and- > > Model.all(:conditions => {:created_at => Date.today..Date.tomorrow}) > > Is the choice just a personal coding preference or is there some > performance or security differences between the two? It''s my > understanding that the array form''s design was to help prevent sql > injection attacks but I was unsure if you lost that benefit by using > the hash form with a range.Doesn''t appear to be a difference in the SQL generated between those two statements. Tested on Rails 3 rc2 -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On 25 August 2010 21:04, Tim Shaffer <timshaffer-BUHhN+a2lJ4@public.gmane.org> wrote:> > > On Aug 25, 3:35 pm, Bob <rpell...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> Apologies if this should be separated into a separate thread but this >> reminded me of a question I had when writing some search methods >> recently. >> >> What, if any, are the differences between the following two find >> statements? >> >> On Aug 20, 2:33 am, Michael Pavling <pavl...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> >> > On 20 August 2010 07:17, Srikanth Jeeva <li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote: >> >> > Model.all(:conditions => ["created_at BETWEEN ? AND ?", Date.today, >> > Date.tomorrow]) >> >> -and- >> >> Model.all(:conditions => {:created_at => Date.today..Date.tomorrow}) >> >> Is the choice just a personal coding preference or is there some >> performance or security differences between the two? It''s my >> understanding that the array form''s design was to help prevent sql >> injection attacks but I was unsure if you lost that benefit by using >> the hash form with a range. > > Doesn''t appear to be a difference in the SQL generated between those > two statements. > > Tested on Rails 3 rc2As a matter of interest does that query include records where created at is exactly Date.tomorrow at time 00:00:00? If it does then it is not actually what the OP wanted I think. Colin -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Bob wrote:> Model.all(:conditions => {:created_at => Date.today..Date.tomorrow})SELECT * FROM "meetings" WHERE ("meetings"."created_at" BETWEEN ''2010-08-26'' AND ''2010-08-27'') Shouldn''t this actually be: Model.all(:conditions => {:created_at => Date.today...Date.tomorrow}) SELECT * FROM "meetings" WHERE ("meetings"."created_at" >= ''2010-08-26'' AND "meetings"."created_at" < ''2010-08-27'') -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.