Database agnostic sql possible?

Rails
1

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?

2

bump

3

gsterndale wrote:

bump

Does this work?

:conditions => [“curtain_at between ? and ?”, Time.now.to_s(:db),
2.hours.from_now.to_s(:db)]

hth

ilan

4

Hi –

On Tue, 18 Mar 2008, Ilan B. 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!

5

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!

6

bump