# Database agnostic sql possible?

**URL:** <https://www.ruby-forum.com/t/database-agnostic-sql-possible/132160>\
**Category:** Rails\
**Created:** [March 17, 2008, 2:55pm UTC](https://www.ruby-forum.com/t/database-agnostic-sql-possible/132160 "2008-03-17T14:55:19Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![gsterndale](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/4bbf92/32.png) [@gsterndale](https://www.ruby-forum.com/u/gsterndale)\
**Post date:** [March 17, 2008, 2:55pm UTC](https://www.ruby-forum.com/t/database-agnostic-sql-possible/132160/1 "2008-03-17T14:55:19Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![gsterndale](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/4bbf92/32.png) [@gsterndale](https://www.ruby-forum.com/u/gsterndale)\
**Post date:** [March 17, 2008, 10:58pm UTC](https://www.ruby-forum.com/t/database-agnostic-sql-possible/132160/2 "2008-03-17T22:58:48Z")

</div>

bump

---

<div class="post-metadata">

**Author:** ![gsterndale](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/4bbf92/32.png) [@gsterndale](https://www.ruby-forum.com/u/gsterndale)\
**Post date:** [March 18, 2008, 2:58am UTC](https://www.ruby-forum.com/t/database-agnostic-sql-possible/132160/3 "2008-03-18T02:58:08Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![gsterndale](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/4bbf92/32.png) [@gsterndale](https://www.ruby-forum.com/u/gsterndale)\
**Post date:** [March 18, 2008, 4:01am UTC](https://www.ruby-forum.com/t/database-agnostic-sql-possible/132160/4 "2008-03-18T04:01:19Z")

</div>

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](http://www.rubypal.com) for details. Berlin dates coming soon!

---

<div class="post-metadata">

**Author:** ![gsterndale](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/4bbf92/32.png) [@gsterndale](https://www.ruby-forum.com/u/gsterndale)\
**Post date:** [March 18, 2008, 2:42pm UTC](https://www.ruby-forum.com/t/database-agnostic-sql-possible/132160/5 "2008-03-18T14:42:36Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![gsterndale](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/4bbf92/32.png) [@gsterndale](https://www.ruby-forum.com/u/gsterndale)\
**Post date:** [March 18, 2008, 8:50pm UTC](https://www.ruby-forum.com/t/database-agnostic-sql-possible/132160/6 "2008-03-18T20:50:06Z")

</div>

bump