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.

Answer 3

Your current query doesn't work because you are querying a date on a datetime column. Those are two different data types.

For a datetime column, you'll need a datetime filter. You can do this by passing a DateTime object to the where clause. However, in your case you want records for the entire day so you'll specify a range between 00:00:00 and 23:59:59.

Before Rails 5.1:

SupportHistory.where(created_at: date.beginning_of_day..date.end_of_day)

Rails 5.1 and onwards: (this will generate the exact same range using #all_day)

SupportHistory.where(created_at: date.all_day)

Answer 4

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.

Answer 5

If you want to parse a specific datetime

SupportHistory.where("created_at>=?", DateTime.parse('10 Dec 2021 11:54:00 PST -08:00'))