I''d like to have database agnostic code, however I have been unsuccessful writing conditions that work for both postgres and mysql for the following: # postgres only conditions = ["curtain_at + CAST (CAST (duration AS VARCHAR) || '' SECOND'' AS INTERVAL) >= ? AND curtain_at < ?", Time.now, Time.now + 2.hours] # MySQL only conditions = ["curtain_at + INTERVAL duration SECOND >= ? and curtain_at < ?", Time.now, Time.now + 2.hours] Concert.find(:all, :conditions => conditions) Can anyone lend some SQL expertise? As a last resort, how can I determine what the current database is so I can selectively set conditions? --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
bump On Mar 17, 9:54 am, gsterndale <gsternd...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I''d like to have database agnostic code, however I have been > unsuccessful writing conditions that work for both postgres and mysql > for the following: > > # postgres only > conditions = ["curtain_at + CAST (CAST (duration AS VARCHAR) || '' > SECOND'' AS INTERVAL) >= ? AND curtain_at < ?", Time.now, Time.now + > 2.hours] > > # MySQL only > conditions = ["curtain_at + INTERVAL duration SECOND >= ? and > curtain_at < ?", Time.now, Time.now + 2.hours] > > Concert.find(:all, :conditions => conditions) > > Can anyone lend some SQL expertise? > > As a last resort, how can I determine what the current database is so > I can selectively set conditions?--~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
gsterndale wrote:> bumpDoes this work? :conditions => ["curtain_at between ? and ?", Time.now.to_s(:db), 2.hours.from_now.to_s(:db)] hth ilan -- 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Hi -- On Tue, 18 Mar 2008, Ilan Berci wrote:> > gsterndale wrote: >> bump > > Does this work? > > :conditions => ["curtain_at between ? and ?", Time.now.to_s(:db), > 2.hours.from_now.to_s(:db)]The ? interpolation mechanism will do the to_s(:db) for you; you just need to provide the Time object. David -- Upcoming Rails training from David A. Black and Ruby Power and Light: ADVANCING WITH RAILS, April 14-17 2008, New York City CORE RAILS, June 24-27 2008, London (Skills Matter) See http://www.rubypal.com for details. Berlin dates coming soon! --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Thanks Ilan and David. While "between" does look like a more elegant solution that the one I posted, the real problem seems to be in adding an integer (cast as an interval) to a datetime in both postgres and mysql. I need to compare two time spans, not just a time and a time span. Hence, the duration column. Thanks again! On Mar 17, 11:00 pm, "David A. Black" <dbl...-0o/XNnkTkwhBDgjK7y7TUQ@public.gmane.org> wrote:> Hi -- > > On Tue, 18 Mar 2008, Ilan Berci wrote: > > > gsterndale wrote: > >> bump > > > Does this work? > > > :conditions => ["curtain_at between ? and ?", Time.now.to_s(:db), > > 2.hours.from_now.to_s(:db)] > > The ? interpolation mechanism will do the to_s(:db) for you; you just > need to provide the Time object. > > David > > -- > Upcoming Rails training from David A. Black and Ruby Power and Light: > ADVANCING WITH RAILS, April 14-17 2008, New York City > CORE RAILS, June 24-27 2008, London (Skills Matter) > Seehttp://www.rubypal.comfor details. Berlin dates coming soon!--~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
bump On Mar 18, 9:41 am, gsterndale <gsternd...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Thanks Ilan and David. While "between" does look like a more elegant > solution that the one I posted, the real problem seems to be in adding > an integer (cast as an interval) to a datetime in both postgres and > mysql. > > I need to compare two time spans, not just a time and a time span. > Hence, the duration column. > > Thanks again! > > On Mar 17, 11:00 pm, "David A. Black" <dbl...-0o/XNnkTkwhBDgjK7y7TUQ@public.gmane.org> wrote: > > > Hi -- > > > On Tue, 18 Mar 2008, Ilan Berci wrote: > > > > gsterndale wrote: > > >> bump > > > > Does this work? > > > > :conditions => ["curtain_at between ? and ?", Time.now.to_s(:db), > > > 2.hours.from_now.to_s(:db)] > > > The ? interpolation mechanism will do the to_s(:db) for you; you just > > need to provide the Time object. > > > David > > > -- > > Upcoming Rails training from David A. Black and Ruby Power and Light: > > ADVANCING WITH RAILS, April 14-17 2008, New York City > > CORE RAILS, June 24-27 2008, London (Skills Matter) > > Seehttp://www.rubypal.comfordetails. Berlin dates coming soon!--~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---