check overlapping start end times, MySQL

Question

Hi, 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 times (free time slots) would be my second question ...

Thank you in advance

any help appreciated Thank You

Answer 1

 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 ...