Home Ask Login Register

Developers Planet

Your answer is one click away!

pramod February 2016

Rails hstore value based query

I want to query an hStore column on PG database and return ONLY the values corresponding to a specific key and value. hstore column contains the information in the below manner

hours: {"tue_1_open"=>"19:30", "tue_1_close"=>"21:45"}

I am able to query based on key and value as follows

Model.where("hours @> hstore(:key,:value )", key: "tue_1_open", value: "19:30")

But I want to query whose value is grater than specific time. I want query something like

Model.where("hours @> hstore(:key,:value )", key: "tue_1_open", value: ">= 19:30")

Any idea how we can achieve this?


Rajarshi Das February 2016

You can try to get greater value in integer not time

Model.where("(hours ? 'tue_1_open')::int > 19")

for datetime hours value of 'tue_1_open' should be a Datetime value

Model.where("(hours ? 'tue_1_open')::timestamp > Time.now")

Post Status

Asked in February 2016
Viewed 1,891 times
Voted 12
Answered 1 times


Leave an answer

Quote of the day: live life