Rails - Comparing Datetime objects (date between two form dates)

Question

I've been struggling with an issue all day and can't figure it out. How do you compare two datetime variables in ruby/rails?

Controller

@events = Event.where('date > ? AND date < ?', params[:timeMin], params[:timeMax])

view: index.html.erb

<%= form_tag events_path, :method => :get do %>
  <p>
    <%= text_field_tag :search, params[:search] %>
    <%= datetime_select :timeMin, params[:timeMin] %>
    <%= datetime_select :timeMax, params[:timeMax] %>
    <%= submit_tag "Search Near", :name => nil %>
  </p>
<% end %>

The Event model has an attribute 'date' which is a datetime object. I've tried converting the params[:timeMin] to a string, but them it makes it very difficult to compare times.

The overarching goal is to search for events that are happening between two times.

thanks

error - happens after search

SQLite3::SQLException: near ",": syntax error: SELECT "events".* FROM "events"  WHERE (date > '---
- (1i)
- ''2014''
','---
- (2i)
- ''1''
','---
- (3i)
- ''8''
','---
- (4i)
- ''14''
','---
- (5i)
- ''36''
' AND date < '---
- (1i)
- ''2014''
','---
- (2i)
- ''1''
','---
- (3i)
- ''13''
','---
- (4i)
- ''14''
','---
- (5i)
- ''36''
') ORDER BY events.created_at DESC

it looks alright to me, could you post some of the actual errors/issues you had with the queries? — Nikolay, Jan 14 '14 at 04:04

score 2 · Accepted Answer · answered Jan 14 '14 at 04:29

You've used datetime_select and it doesn't pass everything in one params[:timeMin], you first have to convert it into DateTime object like this

timemin = DateTime.new(params["timeMin(1i)"].to_i,
                       params["timeMin(2i)"].to_i,
                       params["timeMin(3i)"].to_i,
                       params["timeMin(4i)"].to_i,
                       params["timeMin(5i)"].to_i)

In the same way you need to convert :timeMax into DateTime object. Then you can compare both.

score 0 · Answer 2 · edited May 23 '17 at 12:21

You are passing the params directly to SQL, so it doesn't understand. You need to create a DateTime object from the params. You can see how others have done it here:

Where is the Rails method that converts data from `datetime_select` into a DateTime object?

You can scroll down past the accepted answer to find some example methods. I don't have a Rails console in front of me to try, but I would try time_min = DateTime.new(params[:timeMin]). Rails certainly converts this hash to a date when you save it into a DB column that represents a date. If you wanted to make Rails do it all, you could save both as dates to a temporary table just to get DateTime objects out of it. You could then overwrite them with every new search.

Rails - Comparing Datetime objects (date between two form dates)

2 Answers2