mysql - Rails 5在sql表中找不到日期字段

Question

这个rails sql查询:

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

即使我的数据库中有这个也不起作用:

#<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">

返回结果如下:

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 []>

我也尝试过

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

和

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

所有 3 个查询都返回 nil。

PS:arrival_date_time属于ActiveSupport::TimeWithZone类

Answer 1

我认为您遇到的问题是由于缺乏精确性。尝试以下搜索:

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)

对我来说，我得到了 result_1 和 result_3 的预期结果，但没有得到 result_2。这意味着某个地方存在舍入误差，但只要您的搜索可以容忍正负一秒的精度，您就可以按照我的 result_1 行重写您的查询 - 或者更好的是，转换它到一个范围。