Rails 5中日期范围查询失效问题排查

问题场景 #

我尝试执行以下Rails查询语句，但未返回预期数据：

Requerimiento.where(fecha_hora: 30.day.ago.beginning_of_day..Date.today.end_of_day)

Rails生成的SQL日志 #

Requerimiento Load (52.9ms) SELECT "requerimientos".* FROM "requerimientos" WHERE "requerimientos"."fecha_hora" BETWEEN $1 AND $2 LIMIT $3 [["fecha_hora", "2022-10-10 23:00:00"], ["fecha_hora", "2022-11-10 23:59:59.999999"], ["LIMIT", 11]]

手动执行SQL时的错误 #

该查询本应匹配部分数据，但直接在SQL客户端执行添加了vendedor = 'rm05'条件的SQL语句：

SELECT  "requerimientos".* FROM "requerimientos" WHERE vendedor = 'rm05' AND fecha_hora BETWEEN "2022-10-10 23:00:00" AND "2022-11-10 23:59:59.999999"

出现如下错误：

SQL Error [42703]: ERROR: column "2022-10-10 23:00:00" does not exist

问题原因及解决 #

• 错误原因：在PostgreSQL中，双引号会被解析为列名而非字符串值，你用双引号包裹日期字符串，导致数据库误以为要查找名为2022-10-10 23:00:00的列，从而报错。
• 修正后的SQL：将日期值的双引号替换为单引号即可正常执行

SELECT  "requerimientos".* FROM "requerimientos" WHERE vendedor = 'rm05' AND fecha_hora BETWEEN '2022-10-10 23:00:00' AND '2022-11-10 23:59:59.999999'

• Rails中添加条件的正确方式：无需手动拼接SQL，直接用链式查询更安全且不易出错

Requerimiento.where(vendedor: 'rm05').where(fecha_hora: 30.day.ago.beginning_of_day..Date.today.end_of_day)

内容的提问来源于stack exchange，提问作者victor sosa