check for time overlap (ruby/mysql)
I have 2 columns: start_time and end_time that look like this: "00:00:05", "00:0开发者_如何学Python0:10" (mysql time).
My question follows. How can I validate that one record doesn't overlap any other record already in the table so that it is unique, for example:
"00:00:10", "00:00:20" overlaps "00:00:05", "00:00:15"
Thanks
logically
- neither the start nor end time of your record under test must be between start and end time of any database record - this would cover any partial overlap as well as inside overlap
- an Outside overlap is detected if test_start < db_start AND test_end > db_end
In SQL I would formulate
SELECT count(*) FROM Tab as T WHERE
:param_start BETWEEN T.start_time AND T.end_time OR
:param_end BETWEEN T.start_time AND T.end_time OR
(:param_start <= T.start_time AND
:param_end >= T.end_time
)
if the count is <> 0 you have an overlap
Any record that goes into the database will by nature be unique. If you're trying to validate unique attributes, for example 'login', you must add a 'validates :login, :uniqueness => true' line to the model, but also in the migration file, you will want to add the following line 't.string :login, :unique => true' ... This prevents any overlap
cheers
Paul
select start_time, end_time
from tbl
where start_time < "00:00:20"
and "00:00:10" < end_time
limit 1;
Notes:
Limit 1
is used to quickly return a result, without having to find/count all overlaps- The double start
- when an overlap is found, a sample overlapping record is output
I wrote a gem that could help you solve this problem.
https://github.com/robinbortlik/validates_overlap
At readme is a examle how to use it. But at this time is only for Rails 3.
cheers
Robin Bortlík
精彩评论