Rails: find by day of week with timestamp

Question

I need to grab the records for same day of the week for the preceeding X days of the week. There must be a better way to do it than this:

Transaction.find_by_sql "select * from transactions where EXTRACT(DOW from date) = 1 and organisation_id = 4 order by date desc limit 7"

It gets me what I need but is Postgres specific and not very "Rails-y". Date is a timestamp. Anyone got suggestions?

Answer 1

AFAIK Rails has no any methods to do this by other way. So best, and faster, solution - build DOW index on date column and use your query.

Answer 2

How many days do you want to go back?

I have written a gem called by_star that has a dynamic finder suited for finding up to a certain number of days in the past. If the number of days was always a number you could use this finder:

Transaction.as_of_3_days_ago

If it was dynamic then I would recommend using something such as future or between, depending on if you have transactions in the future (i.e. time travel):

Transaction.future(params[:start_date].to_time)
Transaction.between(params[:start_date].to_time, Time.now)