Rails convert DateTime to date in 'where' query

Question

I need to make a query to database and filter Event by start date, but column type is DateTime. I made scope in model:

scope :day, -> (start_date) {where start_date: start_date}

It works fine for same DateTime value, but I need a filter to get Event only by date, not DateTime.

I have PG database and try:

  scope :day, -> (start_date) {where("start_date:date =?", "#{start_date.to_date}")}  

but I get an error

Answer 1

You may do it like this:

Using SQL Date Function (depends on database this is for MySQL):

scope :on_day -> (start_date) { where("DATE(start_date) = ?", start_date.to_date) }

Or using range (so this will check using date and time both just using the start and end time of the day and executing a BETWEEN query):

scope :day, -> (start_date) { where start_date: start_date.beginning_of_day..start_date.end_of_day }

Or if on Rails 5.1 (this is a new helper introduced in Rails 5.1):

scope :day, -> (start_date) { where start_date: start_date.all_day }

Answer 2

If you want to cast the database value to a date you'll have to do different things for different database servers, like the following for mysql:

scope :day, -> (start_date) {where("DATE(start_date) = ?", start_date)}

May therefore be more sensible to cast your start_date argument to a datetime:

scope :day, -> (start_date) {where start_date: start_date.to_datetime}

Also, BigRon posted an excellent approach to filter between start and end of day and Deep posted a helper for Rails 5.1 that streamlines that approach further, but you really should consider whether your database column actually needs to be datetime if you want to filter by date alone.

Answer 3

You can use a range for the beginning of the day to the end of the day:

scope :day, -> (start_date) {where start_date: start_date.beginning_of_day..start_date.end_of_day}