One of the Rails apps I’ve been working on formats times as HH:MM in the view. No seconds are displayed. This is a pretty common way to format things. When doing timestamp comparisons in SQL, however, the seconds are taken into account. This is bad since it can cause discrepancies in the view.
For example, say I have a table of records with created_at timestamps. My view displays all records with timestamps equal to or before the current time. Let’s assume the current time is 15:00:00 precisely and I happen to have a record with a timestamp of 15:00:00 in the database. The SQL comparison would work fine in this case.
SELECT * FROM records WHERE created_at <= "2010-06-25 15:00:00" => 1 row in set
What if the timestamp in the database is 15:00:03 though? Let’s run the query again.
SELECT * FROM records WHERE created_at <= "2010-06-25 15:00:00" => Empty set
Since 15:00:03 is greater than the current time of 15:00:00, the record doesn’t get returned. This would be fine if we were displaying seconds in the view, but we’re not. From the user’s perspective, the timestamp on the record is still 15:00 and should appear in the view since it’s equal to the current time. But it doesn’t.
One way to fix this would be to handle the time comparisons in Ruby. This is certainly a legitimate option. For this particular project, though, performance was a big issue. (And we all know that Rails can’t scale.) I needed a way to continue letting the database handle the comparisons while disregarding seconds.
The solution I ended up with isn’t ideal (it relies on a couple of functions built into MySQL) but it works fine and runs fast:
SELECT * FROM records WHERE (created_at - INTERVAL SECOND(created_at) SECOND) <= "2010-06-25 15:00:00" => 1 row in set
The number of seconds is extracted from the created_at timestamp and then subtracted from the timestamp. So if the timestamp was 15:00:03, MySQL subtracts 3 seconds to end up with 15:00:00.
This solved the comparison problem for me and made my client very happy. Double win.
Good idea, i guess it works with minutes, hours etc. the same way ? )
This is going to be slow because you won’t be able to use an index on created_at. Why not just query for select * from records where created_at < '2010-06-25 15:01:00'?
Alex, yes, it should work for minutes, hours… any unit that can be extracted from a timestamp with a MySQL function.
Paul, querying for a minute ahead is a good idea, but the time I’m passing to the query isn’t static… it’s the current time. So I would need to perform some sort of calculation to determine if the current time is within 15:00:00 to 15:00:59 and add 1 minute if it was.
This might do the trick.
def end_of_minute(time)
time += 59 - time.sec
end
or this if you want to use the “<" instead "<="
def next_minute(time)
time += 60 - time.sec
end