Rails中实现datetime_utc字段的模糊时间搜索问题

方案1：精准匹配时分秒（推荐） #

通过提取datetime字段的时分秒值进行匹配，避免字符串转换的潜在问题，同时兼容精确时间输入：

if params[:search].present?
  # 匹配HH:MI或HH:MI:SS格式的输入
  time_match = params[:search].match(/^(\d{2}):(\d{2})(?::(\d{2}))?$/)
  if time_match
    hour = time_match[1].to_i
    minute = time_match[2].to_i
    second = time_match[3] ? time_match[3].to_i : 0
    data = data.where(
      "EXTRACT(HOUR FROM datetime_utc) = ? 
       AND EXTRACT(MINUTE FROM datetime_utc) = ? 
       AND EXTRACT(SECOND FROM datetime_utc) = ?",
      hour, minute, second
    )
  else
    # 尝试精确匹配，捕获日期格式转换失败的异常
    begin
      data = data.where(datetime_utc: params[:search])
    rescue ActiveRecord::StatementInvalid
      # 转换失败时返回空数据集
      data = data.none
    end
  end
end

方案2：修正to_char函数的SQL写法 #

若偏好字符串模糊匹配，用Arel构建查询避免手动SQL的语法错误：

if params[:search].present?
  # 替换为你的模型类名，比如Candlestick
  table = YourModel.arel_table
  formatted_time = Arel::Nodes::NamedFunction.new(
    'TO_CHAR',
    [table[:datetime_utc], Arel::Nodes.build_quoted('YYYY-MM-DD HH24:MI:SS')]
  )
  data = data.where(formatted_time.matches("%#{params[:search]}%"))
end

关键说明 #

• 确保datetime_utc字段类型为PostgreSQL的timestamp（对应Rails的:datetime类型），否则时间函数无法正常工作。
• 原报错PG::UndefinedColumn大概率是手动SQL字符串的解析问题，用Arel写法可避免此类错误。
• 异常捕获逻辑能处理用户输入无效日期格式的情况，防止数据库抛出转换错误。

内容的提问来源于stack exchange，提问作者A. Vreeswijk