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

Question

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.

@jvillian, yeah, they are very similar questions, but the context of that one is "no results appear", while the context of mine is "it is producing an incorrect query". ¯\_(ツ)_/¯ — Steven Jeffries
– Steven Jeffries, Commented Aug 1, 2018 at 20:14
Recognizing the difference in contexts, is the approach applicable? VisitToGrandma.where(visit_date: 1.month.ago..Time.now)? — jvillian
– jvillian, Commented Aug 1, 2018 at 20:16
@jvillian, for this fictional example, yes, but I need to know how to include date ranges in order to use all of postgresql's daterange operators, not just find if a date is in this range — Steven Jeffries
– Steven Jeffries, Commented Aug 1, 2018 at 20:17
Apologies for my confusion, but do you mean when the field type is a postgres daterange? — jvillian
– jvillian, Commented Aug 1, 2018 at 20:30

mu is too short · Accepted Answer · 2018-08-01 21:32:18Z

5

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)
)

edited Aug 1, 2018 at 21:32

answered Aug 1, 2018 at 21:16

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Steven Jeffries Over a year ago

Using the connection.type_cast made it happy. Thanks. :)

Edgar Ortega · Accepted Answer · 2018-08-02 04:18:17Z

2

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

edited Aug 2, 2018 at 4:18

answered Aug 1, 2018 at 21:10

Edgar Ortega

1,74520 silver badges23 bronze badges

3 Comments

mu is too short Over a year ago

Why use tsrange when daterange is available and we're dealing with dates? You can use connection.type_cast to produce a string in the appropriate format, #type_cast also properly differentiates between .. and ... ranges.

Edgar Ortega Over a year ago

You're right @muistooshort, daterange is the correct operator here to compare dates, not timestamps. I'll update my answer thanks. Woudl you mind posting another answer with your suggestions?

mu is too short Over a year ago

I think my existing answer covers it well enough but feel free to update yours.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related