# Best practices to get DB-agnostic datetime comparisons?

**URL:** <https://www.ruby-forum.com/t/best-practices-to-get-db-agnostic-datetime-comparisons/146937>\
**Category:** Rails\
**Created:** [August 26, 2008, 11:40pm UTC](https://www.ruby-forum.com/t/best-practices-to-get-db-agnostic-datetime-comparisons/146937 "2008-08-26T23:40:13Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [August 26, 2008, 11:40pm UTC](https://www.ruby-forum.com/t/best-practices-to-get-db-agnostic-datetime-comparisons/146937/1 "2008-08-26T23:40:13Z")

</div>

All,

I have some simple filtering that I would like to do by date. I’m  
currently running against MySQL, but will likely be running this app. in  
production against Oracle.

Currently, I have a condition like the following set up:

‘created\_at \>= ?’, “#{params[:days].to\_i.days.ago.strftime(’%Y-%m-%d  
%H:%M:%S’)}”

which forces the # of days ago into a default MySQL date formatted  
string for comparison with the MySQL date.

However, this implicitly binds me to MySQL’s concept of a default date  
format, and it is probably different for Oracle.

Is there any way to get around the specificity of the date format in  
order to be able to move to another DB without modifying the code?

Even if I use a date function to calculate a value that can be compared  
to what is in my app., that would also almost certainly be DB specific  
function, so I would have the same problem.

Any thoughts are appreciated.

Wes

---

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [August 26, 2008, 11:49pm UTC](https://www.ruby-forum.com/t/best-practices-to-get-db-agnostic-datetime-comparisons/146937/2 "2008-08-26T23:49:50Z")

</div>

On Aug 26, 10:40 pm, Wes G. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> which forces the # of days ago into a default MySQL date formatted  
> string for comparison with the MySQL date.
> 
> However, this implicitly binds me to MySQL’s concept of a default date  
> format, and it is probably different for Oracle.
> 
> Is there any way to get around the specificity of the date format in  
> order to be able to move to another DB without modifying the code?

Use connection.quote ?

Fred

---

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [August 27, 2008, 12:06am UTC](https://www.ruby-forum.com/t/best-practices-to-get-db-agnostic-datetime-comparisons/146937/3 "2008-08-27T00:06:15Z")

</div>

Nice!

Ended up with:

params[:days].to\_i \> 0 ? [‘created\_at \>= ?’,  
Invoice.connection.quoted\_date(params[:days].to\_i.days.ago)] : []

Many thanks,  
Wes

---

<div class="post-metadata">

**Author:** ![weyus](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/7ab992/32.png) [@weyus](https://www.ruby-forum.com/u/weyus)\
**Post date:** [August 27, 2008, 12:11am UTC](https://www.ruby-forum.com/t/best-practices-to-get-db-agnostic-datetime-comparisons/146937/4 "2008-08-27T00:11:31Z")

</div>

On Aug 26, 2008, at 5:40 PM, Wes G. wrote:

> %H:%M:%S’)}"  
> You want just:  
> :conditions =\> [‘created\_at \>= ?’, params[:days].to\_i.days.ago]

Let the db adapter handle the conversion.

If you really want to get a string, then use .to\_s(:db) rather than  
strftime

-Rob

> compared  
> to what is in my app., that would also almost certainly be DB specific  
> function, so I would have the same problem.
> 
> Any thoughts are appreciated.
> 
> Wes

Rob B. [http://agileconsultingllc.com](http://agileconsultingllc.com)  
removed\_email\_address@domain.invalid