CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
eibersji
eibersji

Reputation: 1216

How to remove weekends from ruby query using postgres

So I have this query, where I find packages that exceed the merchant threshold. My problem is that I don't want to include weekends on the count. So if my package is created on a Friday, and the threshold is 2 days. This query will return the package when I check for it on Monday since it counts weekends as well. Is there a way that I can exclude weekends?

Package.joins(:merchant).where(
  "packages.created_at + interval '1 day' * merchants.threshold < ?",Time.zone.now
)

I tried

Package.joins(:merchant).where(
  "packages.created_at + interval '1 day' * merchants.threshold < ?",Time.zone.now
).where("extract(dow from packages.created_at) not in (6,0)")

but it does not work.

Is there a way that I can use to make the query only count weekdays like Mon, Tue, Wed, Thurs, Fri, then Mon again instead of Mon, Tue, Wed, Thurs, Fri, Sat, Sun then Mon again

Upvotes: 0

Views: 111

Answers (1)

eux
eux

Reputation: 3282

You could exclude weekends by EXTRACT(ISODOW FROM *) function:

Package
  .joins(:merchant)
  .select("*, packages.created_at + interval '1 day' * merchants.threshold AS datetime")
  .where("EXTRACT(ISODOW FROM datetime) NOT IN (6, 7) AND datetime < ?", Time.zone.now)

Upvotes: 0

Related Questions