CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-5rails-activerecord
Steven Jeffries
Steven Jeffries

Reputation: 3572

How to parameterize a date range in a rails (postgres) query?

Just like the title asks, how do I include a date range in a query?

For example, let's say I have a model called VisitToGrandma and it has a field called visit_date. Now, I want to find all visits to grandmothers that happened in the last month.

Here is what I'm trying:

VisitToGrandma.where("? @> visit_date", (1.month.ago..Time.now))

However, this produces the query:

SELECT "visits_to_grandmas".* FROM "visits_to_grandmas" WHERE ('2018-07-01','2018-07-02','2018-07-03','2018-07-04','2018-07-05','2018-07-06','2018-07-07','2018-07-08','2018-07-09','2018-07-10','2018-07-11','2018-07-12','2018-07-13','2018-07-14','2018-07-15','2018-07-16','2018-07-17','2018-07-18','2018-07-19','2018-07-20','2018-07-21','2018-07-22','2018-07-23','2018-07-24','2018-07-25','2018-07-26','2018-07-27','2018-07-28','2018-07-29','2018-07-30','2018-07-31','2018-08-01' @> visit_date)

What's the correct way to parameterize this?

Yes, I know for this fictional example, I can just use the start and end date and use BETWEEN or some other operators to do this without using an actual date range, but that's not what I'm asking about.

Edit to point out how this question is different from the other question:

That question is just about finding a date in a range, my question is asking how to actually parameterize a range object so that all of postgresql's daterange operators can be used.

Upvotes: 2

Views: 1955

Answers (2)

Edgar Ortega
Edgar Ortega

Reputation: 1735

It seems you'll need to abuse a bit of where placeholders to accomplish this with ActiveRecord

VisitToGrandma.where(
  ":range::daterange @> date",
  range: '[2011-01-01,2011-03-01)'
)

Note the symbols [ and ) in the range, more information on the Postgres doc https://www.postgresql.org/docs/current/static/rangetypes.html#RANGETYPES-IO

Upvotes: 2

mu is too short
mu is too short

Reputation: 434606

As I recently mentioned in another PostgreSQL/range/ActiveRecord answer, AR's integration with PostgreSQL's range types is limited.

You can get around that by semi-manually producing the string representation of the range you want:

ActiveRecord::Base.connection.type_cast(1.month.ago.to_date .. Time.now.to_date)

Not the #to_date calls to get dates rather than timestamps. You could also say:

today = Date.today
ActiveRecord::Base.connection.type_cast(today.months_ago(1) .. today)

Mixing that in with your query:

today = Date.today
VisitToGrandma.where(
  '? @> visit_date',
  VisitToGrandma.connection.type_cast(today.months_ago(1) .. today)
)

Depending on context, you might have easier/cleaner access to connection. You might want to include a typecast just to be sure nothing is misinterpreted:

today = Date.today
VisitToGrandma.where(
  '?::daterange @> visit_date',
  VisitToGrandma.connection.type_cast(today.months_ago(1) .. today)
)

Upvotes: 5

Related Questions