Best practices to get DB-agnostic datetime comparisons?

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

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

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

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