Sorry if that question sounds strange, but I'm diving into Rails and I'm still learning the jargon. Basically, I'm trying to create a single-pass query that uses the value of one of the model's attributes in a calculation in the query (assuming that's even possible).
I have a
Tournament model that has a
start_date attribute that is a
DateTime object. I'm trying to create a query that returns all the Tournaments that have a start_date no older than 1 hour + the length of the tournament, or put another way, all tournaments that haven't yet started or have started, but haven't ended longer than an hour ago. My current query, which doesn't work, looks like this...
validTourneys = Tournament.where("start_date > (? - duration_in_mins)", (DateTime.now.utc - 1.hour))
duration_in_mins is an integer attribute of the Tournament model, but this query doesn't work and it seems to be returning all the Tournaments all the time. I'd like to include
duration_in_mins in the
(DateTime.now.utc - 1.hour) part of the calculation, but I don't know how to reference it, which is why I included it in the string part of the query, hoping that would work. Am I at least on the right track?
I should mention I'm using SQLite for development and PostgreSQL for production.
Thanks for your wisdom!
The problem is that if you subtract minutes from a DateTime object, you are not subtracting minutes but days.
# This works as expected
dt = DateTime.now # Thu, 28 Apr 2011 09:55:14 +0900
an_hour_ago = dt - 1.hour # Thu, 28 Apr 2011 08:55:14 +0900
# But, this does not...
two_hours_in_minutes = 120
two_hours_ago = dt - two_hours_in_minutes # Wed, 29 Dec 2010 09:55:14 +0900
In the last example 120 days are subtracted instead of minutes. This is probably also happening in your query. You have to convert
duration_in_minutes to days and then subtract.
I don't know enough about SQL to answer your question directly (I think this will probably also depend on what database you're using, so you might want to mention that).
Have you considered, though, having
end_date as DateTime columns instead of
duration_in_mins? If this is going to be a common query, that would certainly make it more performant, as well as making your code easier to read and understand.
This query will only work if your database is smart enough to know how to add (what I am assuming) is a DateTime and and integer. And I can't think of a database that will do that correctly the way you have it coded. No database will assume minutes. Some might do ticks, seconds, or days.
This part of the calculation
(? - duration_in_mins)
is going to happen on the database, not in Ruby-land.