Hi, I have a flight model. I want to find all flights with a specific arrival airport, a specific departure airport and which depart on a certain date. My problem is that the departure date is a datetime column in the db and I wish to search this column by date (not datetime). I have written the following: @flights = Flight.find(:all, :conditions => [''arrival_airport_id = ? and departure_airport_id = ? and departure_datetime >= ? and departure_datetime < ?'', @arrival_airport_id, @departure_airport_id, @date, @date+1]) This works but the code seems long and ugly. Is this an acceptable way to do what I want, or can someone point me to a better, ''prettier'' method? Thanks very much in advance. -- 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 Jan 7, 2011, at 8:48 AM, Jim Burgess wrote:> Hi, > > I have a flight model. > I want to find all flights with a specific arrival airport, a specific > departure airport and which depart on a certain date. > > My problem is that the departure date is a datetime column in the db and > I wish to search this column by date (not datetime). > > I have written the following: > > @flights = Flight.find(:all, :conditions => [''arrival_airport_id = ? and > departure_airport_id = ? and departure_datetime >= ? and > departure_datetime < ?'', @arrival_airport_id, @departure_airport_id, > @date, @date+1]) > > This works but the code seems long and ugly. > > Is this an acceptable way to do what I want, or can someone point me to > a better, ''prettier'' method?You could replace "departure_datetime >= ? and departure_datetime < ?" with "departure_datetime BETWEEN ? AND ?"... Doesn''t help much though... -- 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.
Jim Burgess wrote in post #973132:> I have a flight model. > I want to find all flights with a specific arrival airport, a specific > departure airport and which depart on a certain date. > > My problem is that the departure date is a datetime column in the db and > I wish to search this column by date (not datetime). > > @flights = Flight.find(:all, :conditions => [''arrival_airport_id = ? and > departure_airport_id = ? and departure_datetime >= ? and > departure_datetime < ?'', @arrival_airport_id, @departure_airport_id, > @date, @date+1])Rails 2: @flights = Flight.find(:conditions => { :arrival_airport_id => departure_airport_id, :departure_datetime => @date...(@date + 1.day) }) Rails 3: @flights = Flight.where(:arrival_airport_id => departure_airport_id, :departure_datetime => @date...(@date + 1.day)) SELECT "flights".* FROM "flights" WHERE ("flights"."arrival_airport_id" = 1) AND ("flights"."departure_datetime" >= ''2011-01-07 17:21:56.932566'' AND "flights"."departure_datetime" < ''2011-01-08 17:21:56.932568'') -- 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.
Robert Walker wrote in post #973147:> Rails 2: > @flights = Flight.find(:conditions => { :arrival_airport_id => > departure_airport_id, :departure_datetime => @date...(@date + 1.day) }) > > Rails 3: > @flights = Flight.where(:arrival_airport_id => departure_airport_id, > :departure_datetime => @date...(@date + 1.day)) > > SELECT "flights".* FROM "flights" WHERE ("flights"."arrival_airport_id" > = 1) AND ("flights"."departure_datetime" >= ''2011-01-07 17:21:56.932566'' > AND "flights"."departure_datetime" < ''2011-01-08 17:21:56.932568'')Oops, The above statements are actually correct but the resulting SQL show here was incorrect. Should have been: SELECT "flights".* FROM "flights" WHERE ("flights"."arrival_airport_id" = 1) AND ("flights"."departure_datetime" >= ''2011-01-07'' AND "flights"."departure_datetime" < ''2011-01-08'') -- 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 7 January 2011 17:30, Robert Walker <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> Robert Walker wrote in post #973147: >> Rails 2: >> @flights = Flight.find(:conditions => { :arrival_airport_id => >> departure_airport_id, :departure_datetime => @date...(@date + 1.day) }) >> >> Rails 3: >> @flights = Flight.where(:arrival_airport_id => departure_airport_id, >> :departure_datetime => @date...(@date + 1.day)) >> >> SELECT "flights".* FROM "flights" WHERE ("flights"."arrival_airport_id" >> = 1) AND ("flights"."departure_datetime" >= ''2011-01-07 17:21:56.932566'' >> AND "flights"."departure_datetime" < ''2011-01-08 17:21:56.932568'') > > Oops, The above statements are actually correct but the resulting SQL > show here was incorrect. Should have been: > > SELECT "flights".* FROM "flights" WHERE ("flights"."arrival_airport_id" > = 1) AND ("flights"."departure_datetime" >= ''2011-01-07'' AND > "flights"."departure_datetime" < ''2011-01-08'')I think the above would only be correct if the OP is operating in UTC, generally he should make @date the UTC time for the start of the local day as the datetimes in the db will be in utc. 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Colin Law wrote in post #973150:> On 7 January 2011 17:30, Robert Walker <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote: >>> = 1) AND ("flights"."departure_datetime" >= ''2011-01-07 17:21:56.932566'' >>> AND "flights"."departure_datetime" < ''2011-01-08 17:21:56.932568'') >> >> Oops, The above statements are actually correct but the resulting SQL >> show here was incorrect. Should have been: >> >> SELECT "flights".* FROM "flights" WHERE ("flights"."arrival_airport_id" >> = 1) AND ("flights"."departure_datetime" >= ''2011-01-07'' AND >> "flights"."departure_datetime" < ''2011-01-08'') > > I think the above would only be correct if the OP is operating in UTC, > generally he should make @date the UTC time for the start of the local > day as the datetimes in the db will be in utc.Good catch, thanks Colin. -- 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.
Thanks very much for all of the answers. I went with :departure_datetime => @date...(@date + 1.day) as this was exactly what I was after. Thanks also for highlighting the difference between Rails 2 and Rails 3. I look forward to giving Rails 3 a try. Best, Jim -- 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.