Best practices to get DB-agnostic datetime comparisons?

Rails
1

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

2

On Aug 26, 10:40 pm, Wes G. [email protected]
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

3

Nice!

Ended up with:

params[:days].to_i > 0 ? [‘created_at >= ?’,
Invoice.connection.quoted_date(params[:days].to_i.days.ago)] : []

Many thanks,
Wes

4

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
[email protected]