CODEWITHSUNDEEP
mysqlruby-on-railsruby
chaosfirebit
chaosfirebit

Reputation: 111

Rails 5 not finding date field in sql table

This rails sql query: 

Flight.where(arrival_date_time: "2017-04-02 23:29:43".to_datetime)

Does not work even though I have this in my database: 

#<Flight id: 1, departed_from_id: 1, arriving_to_id: 3, departure_date_time: "2017-04-02 16:29:43", arrival_date_time: "2017-04-02 23:29:43", created_at: "2017-04-02 13:29:43", updated_at: "2017-04-02 13:29:43">

It returns with this: 

Flight Load (0.2ms)  SELECT "flights".* FROM "flights" WHERE "flights"."arrival_date_time" = ?  [["arrival_date_time", 2017-04-02 23:29:43 UTC]]
 => #<ActiveRecord::Relation []>

I have also tried 

Flight.find_by(arrival_date_time: "2017-04-02 23:29:43".to_datetime)

and

Flight.where("arrival_date_time = ?  ", "2017-04-02 23:29:43".to_datetime)

All 3 queries return nil.

PS: arrival_date_time is of class ActiveSupport::TimeWithZone

Upvotes: 0

Views: 67

Answers (1)

Brian
Brian

Reputation: 5491

I think you're encountering an issue due to a lack of precision. Try the following searches:

search_time = "2017-04-02 23:29:43".to_datetime
result_1 = Flight.where("arrival_date_time between ? and ?", search_time - 1.second, search_time + 1.second)
result_2 = Flight.where("arrival_date_time between ? and ?", search_time - 1.second, search_time)
result_3 = Flight.where("arrival_date_time between ? and ?", search_time, search_time + 1.second)

For me, I get the expected result for result_1 and result_3, but not result_2. This implies that there's a rounding error someplace, but that as long as your search can tolerate a precision of plus or minus one second, you can rewrite your query as in my result_1 line - or better yet, convert it to a scope.

Upvotes: 1

Related Questions