check overlapping start end times, MySQL

Im stuck with this and need some help.

SELECT COUNT(id) AS counter  
  FROM time_reporter  
 WHERE user_id='$user_id' 
   AND actual_date = '$date' 
   AND  ((start_time > '$start_time' AND end_time < '$end_time'))

It checks ok if in range but i need to make sure if there is a record start , end time so next record could not be inserted ...

if one record has end_time example 22:30 the next record can not have start_time 22:15 etc ... so with this i cant get is count ...

So next record will not overlap or interfere any existing time-slots within same day and by same user ... Also would be nice to get available range of t开发者_如何学JAVAimes (free time slots) would be my second question ...

Thank you in advance

any help appreciated Thank You

 SELECT COUNT(id) AS counter FROM time_reporter WHERE user_id='$user_id' AND actual_date = '$date' AND ((start_time > '$start_time' AND end_time < '$end_time') OR (end_time > '$start_time' AND end_time < '$end_time'))

I think found a solution the above code prevents start_time, end_time overlapping ...

eaxmple

22:00 22:15

22:15 23:15

but this is wrong again ... 23:00 23:45

there might be improvements to make it bulletproof ...

Try this query for find overlap task time on mysql

SELECT * FROM your_table Where (('2014-02-05 20:00:00' <= end_time and '2014-02-05 20:00:00' > start_time) OR (IF(('2014-02-05 08:00:00' < end_time),IF('2014-02-05 08:00:00'>=start_time,false,IF('2014-02-05 20:00:00'>start_time,true,false)) ,false)) OR ('2014-02-05 08:00:00' =start_time and '2014-02-05 20:00:00'=end_time)) and user_id =116