Rails ActiveRecord Find / Search by Date

Question

I am trying to find records by 'created_at' date - the database column type is 'datetime' and I am using the UI DatePicker from jQuery

my url look like this: "localhost:3000/users_supported?selected_date=2012-10-31"

So far i am doing good :) and my query in controller looks like this:

@support_histories = current_agent.support_histories.where(:created_at => Date.parse(params[:selected_date]))

How to properly extract records by 'date' only from the 'datetime' db column

I tried doing this in Rails Console, but no luck there:

sh = SupportHistory.where(:created_at => DateTime.parse('2012-10-31'))
sh = SupportHistory.where(:created_at => Date.parse('2012-10-31'))
sh = SupportHistory.where(:created_at => DateTime.strptime('2012-10-31', '%Y-%m-%d'))

I got records if i do like mentioned below, but that's not useful to me as i am trying to find record by 'date' not by 'DateTime'

sh = SupportHistory.where(:created_at => '2012-10-31 19:49:57')

Answer 1

selected_date = Date.parse(params[:selected_date])
# This will look for records on the given date between 00:00:00 and 23:59:59
sh = SupportHistory.where(
       :created_at => selected_date.beginning_of_day..selected_date.end_of_day)

Time Zones may be a concern you need to look into, but this should work if all your times are in the same time zone.

Answer 2

A simple solution I use sometimes is to cast the date(time) field as text on the database rather than parse the string into date on application side. For your case that would be:

where('CAST(created_at AS text) LIKE ?', params[:selected_date])

Might not be the most effective on the database (depending on the context you use it in) but saves a lot of pita on the application side.