How to remove weekends from ruby query using postgres

Question

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

eux eux · Accepted Answer · 2021-03-11T04:45:15

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)

How to remove weekends from ruby query using postgres

1 Answers