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:

1. Limit 1 is used to quickly return a result, without having to find/count all overlaps
2. The double start
3. 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