0

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

chaosfirebit
  • 111
  • 2
  • 8
  • Possible duplicate of [Equals(=) vs. LIKE for date data type](http://stackoverflow.com/questions/18505277/equals-vs-like-for-date-data-type) – Brian Apr 04 '17 at 16:34
  • @Brian I don't see how that's a duplicate: different database, different types. – mu is too short Apr 04 '17 at 16:37
  • Have you checked `Flight.where(arrival_date_time: "2017-04-02 23:29:43".to_datetime).to_sql` to make sure you're running the query you think you are? – mu is too short Apr 04 '17 at 16:39
  • @muistooshort am pretty sure it is sql after all it returns this: `Flight Load (0.2ms) SELECT "flights".* FROM "flights" WHERE "flights"."arrival_date_time" = ? [["arrival_date_time", 2017-04-02 23:29:43 UTC]] => #` – chaosfirebit Apr 04 '17 at 16:44
  • @Brian Please double check my question before marking as duplicate thanks – chaosfirebit Apr 04 '17 at 16:48
  • Is arrival_date_time a string or date object? Is to_datetime doing what you think it is? You're far better off using rails strptime method to specify the exact format the string you're converting is in to ensure you get the right result. – bkunzi01 Apr 04 '17 at 17:10
  • @bkunzi01 believe me or not, try searching via a date yourself with any model with a datetime. It will NOT WORK – chaosfirebit Apr 04 '17 at 17:14
  • If you're searching by date on a column with datetime you then need to do a search by range...for example all datetimes between day + 00 hours and day +23:59 hours. Eg. @flights = Flight.where(arrival_date_time: DateTime.now.beginning_of_day.. DateTime.now.end_of_day) – bkunzi01 Apr 04 '17 at 17:26
  • @chaosfirebit I think the point in the question I linked may still apply - you may be trying to compare a string against a date and having equality issues due to a loss of precision. – Brian Apr 04 '17 at 20:40

1 Answers1

1

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.

Brian
  • 5,300
  • 2
  • 26
  • 32