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

Solution 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.

Solution 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.

Solution 3

I solved this problem by creating a method in model like below, Say, my model is ticket.rb

 def created_date
   self.created_at.to_date
 end

then I queried like this,

 selected_date = Date.parse(params[:date])

 Ticket.all.map{|t| t if t.created_date == selected_date}.compact

This gives me accurate results that matches with the chosen date by the user.

Comments

[Syed]

I have no words to thank you but i have to say I LOVE YOU ;) - i am New to coding and was trying to do this entire day :) I still would like to know - is there a way to query db excluding time ?

[Jason Noble]

Section 2.2.2 of guides.rubyonrails.org/active_record_querying.html is similar to the answer I gave. You could probably do something creative like select("date(created_at) as ordered_date).where(:ordered_date => ____) but I like the beginning_of_day..end_of_day solution.

[David Escobar]

This is not a good way to do it because "Ticket.all" gets all the tickets in the table and expands them into an Array in memory. Bad for memory consumption and performance. Also, instead of doing "map" followed by "compact" (most likely looping through the entire ticket collection twice!), just use the "select" method, which filters the collection in one pass. But in the end, Jason Noble's answer above is the right way to do it, as "where" does not expand everything into memory the way "all" does.

[Constant Meiring]

This is such an elegant, Railsy solution. :)