如何选择行覆盖今天的日和月以pg/Rails的

Question

所以我这个表称为SeasonRate

create_table "season_rates", force: :cascade do |t| 
    t.string "title" 
    t.datetime "from_date" 
    t.datetime "to_date" 
    t.float "rate" 
    t.integer "property_type_id" 
    t.datetime "created_at",  null: false 
    t.datetime "updated_at",  null: false 
    t.index ["property_type_id"], name: "index_season_rates_on_property_type_id", using: :btree 
end 

我希望能够在其中找到今天的日期行from_date和to_date之间落在忽视那一年。到目前为止，使用this我已经能够编写代码

def todays_rate(apartment) 
    property_type = apartment.property_type_id 
    @rate = SeasonRate.where(property_type_id: property_type) 
    @todays_rate = @rate.where("extract (DOY FROM TIMESTAMP from_date) <= extract (DOY FROM TIMESTAMP ?) AND extract (DOY FROM TIMESTAMP to_date) >= extract (DOY FROM TIMESTAMP ?)",Date.today, Date.today) 
end 

但它给了我错误说

PG::SyntaxError: ERROR: syntax error at or near "from_date" LINE 1: ...ty_type_id" = $1 AND (extract (DOY FROM TIMESTAMP from_date)... ^ : SELECT "season_rates".* FROM "season_rates" WHERE "season_rates"."property_type_id" = $1 AND (extract (DOY FROM TIMESTAMP from_date) <= extract (DOY FROM TIMESTAMP '2017-08-04') AND extract (DOY FROM TIMESTAMP to_date) >= extract (DOY FROM TIMESTAMP '2017-08-04'))

...我已经寻找这个问题很多，但我无法找到任何解决方案。请帮助我，我在这里做错了什么，或者如果你有这个问题的更好的解决方案，它会很棒。非常感谢你。

Answer 1

我可能是错的，但它看起来像添加TIMESTAMP告诉Postgres将字符串转换为时间戳，然后你没有给它一个字符串，你给它一个列名，因此一个语法错误：

select EXTRACT(DOY FROM TIMESTAMP created_at), created_at from users; 
ERROR: syntax error at or near "created_at" 
LINE 1: select EXTRACT(DOY FROM TIMESTAMP created_at), created_at fr... 

，但你可以给它，而不是一个字符串转换的列名，它似乎做工精细

select EXTRACT(DOY FROM created_at), created_at from users; 
date_part |   created_at 
-----------+---------------------------- 
     190 | 2017-07-09 21:07:41.746157