Filtering by date substrings in Rails

Question

We have a grid control that allows a user to filter text in different fields. A number of these fields might be timestamps, like created_at and updated_at.

Now, a user can do many different types of filters, including >, <, >=, <=, etc. So, a query that comes across from the control might look like:

"created_at > 2009", or "created_at >= 2006 and created_at <= 2010".

The database doesn't recognize years as valid timestamps, nor does rails automatically convert them as such. Are there any plugins or idiomatic ways to accomplish taking a year, or better yet, portion of a timestamp, making a best guess at the meaning, and converting it into a usable database timestamp?

Answer 1

Have a look at the following projects

• searchlogic
• pacecar

For example, searchlogic supports conditions like the following one.

Post.all(:conditions => { :hour_of_created_at => 9 })

Answer 2

created_at > 2009

Post.all(:conditions => ["created_at > ?", Date.new(y = 2010, m = 1, d = 1).to_s])

created_at >= 2006

Post.all(:conditions => ["created_at >= ?", Date.new(y = 2006, m = 1, d = 1).to_s])

created_at <= 2010

Post.all(:conditions => ["created_at <= ?", Date.new(y = 2010, m = 12, d = 31).to_s])

Something like this perhaps?

Answer 3

you can use ruby's Time class

Time.local(2010) # => Fri Jan 01 -0600 2010

or

Time.gm(2010) # => Fri Jan 01 00:00:00 UTC 2010