Best practices to get DB-agnostic datetime comparisons?

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

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?

Use connection.quote ?

Fred

Nice!

Ended up with:

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

Many thanks,
Wes

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')}"

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

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

Rob Biedenharn http://agileconsultingllc.com
Rob@AgileConsultingLLC.com