Rails - Aug 2008 - 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
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Aug 26, 10:40 pm, Wes Gamble
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
> 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
>
> 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.
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Nice!

Ended up with:

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

Many thanks,
Wes
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Aug 26, 2008, at 5:40 PM, Wes Gamble wrote:
> 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-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org