database agnostic sql possible?

rubyonrails-talk
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 --

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