Rocco Rocco - 1 year ago 66
SQL Question

How to query based on moduloed date

Writing a task that will run daily, but only looking for users where created_at is at week long increments ago. I want to do something along the lines of

User.where("created_at.days_ago % 7 = 0")

How might I do this?


For reference the task is for verifying a user's email. They can continue using the product without verifying for some amount of time, but I want to email them periodically (once per week) to verify. I'm using the heroku scheduler to do this and the max time between runs it allows is 1 day, which is why I need only the people who are on exactly 1 week increments from when they were created

Answer Source

You could look at generating a list of the dates themselves, using something along the lines of:

((User.minimum(:created_at).to_date)..({|d| ( - d) % 7 == 0}

Since created_at is a timestamp you'd probably need to apply a SQL function to it, to truncate it to a date.

days = (({|d| ( - d) % 7 == 0}
User.where("created_at::date in (?)", days)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download