Rocco Rocco - 1 month ago 8
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?

EDIT

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

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

((User.minimum(:created_at).to_date)..(Date.today)).to_a.select{|d| (Date.today - 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 = ((Date.today-1.years)..(Date.today)).to_a.select{|d| (Date.today - d) % 7 == 0}
User.where("created_at::date in (?)", days)
Comments