Help in logic to not overlap a band of schedules already exists at the base

Question

Good morning. As suggested by a community member, in the post that started this, I come to ask for collaboration in a logic. I have a table that contains the fields start time and hora_termino. I need a routine that makes it impossible for the user to enter a time zone that overlaps existing ones, regardless of the day. Any time slot between 24 hours can therefore be inserted. Imagine that the table is filled with these values:

   inicio        termino
1) '07:00:00' - '09:00:00'
2) '09:00:00' - '11:30:00'
3) '12:00:00' - '15:35:00'
4) '22:35:00' - '01:49:00'

The set of parameters below could not be inserted as they overlap the existing schedules.

Não poderia ser inserido:
   inicio        termino
1) '07:00:01' - '08:00:00'
2) '10:00:00' - '11:32:00'
3) '06:00:00' - '23:00:00'

In short, a time cannot overlap an existing one.

I would like to create an SQL statement or a Stored Procedure that returns true or false, saying whether or not to insert the time range sent by the start and end parameters. The times below could be entered:

   inicio        termino
1) '06:00:00' - '06:30:00'
2) '11:30:00' - '12:00:00'
3) '15:35:00' - '16:12:00'

An important observation is that a time slot can be inserted starting at the same time that another already registered one ends. It can stay this way:

   inicio        termino
1) '06:00:00' - '12:00:00'
2) '12:00:00' - '18:00:00'
3) '18:00:00' - '00:00:00'
4) '00:00:00' - '06:00:00'

Thanks in advance for the support.

Answer 1

You can check each time using the clause BETWEEN from the database. To correctly handle dates when the start time is lower than the end, we must add a period.

Take an example:

SELECT
COUNT(*) AS qtd

FROM tempo
WHERE TIME_TO_SEC('23:52:00') BETWEEN 
TIME_TO_SEC(inicio) AND 

(TIME_TO_SEC(termino) + IF(inicio > termino, 24 * 60 * 60, 0))

The secret of the query is to convert the schedules to seconds with the function TIME_TO_SEC. The treatment of shorter hours is done by adding one more day (in seconds) to the total time.

To correctly check the value, we must have a query for each of the parameters informed, and the final count must return a zeroed value. A complete and functional example of overlap:

SET @inicio = '06:00:00';
SET @termino = '10:00:00';

SET @inicioSegundos = TIME_TO_SEC(@inicio);
SET @terminoSegundos = TIME_TO_SEC(@termino);

/* A clausula BETWEEN inclui o valor do inicio, portanto podemos adicionar 1 segundo para evitar este problema */
SET @inicioSegundos = @inicioSegundos + 1;

SET @umDia = 24 * 60 * 60;

SELECT
COUNT(*) qtd
FROM tempo
WHERE 
  (@inicioSegundos  BETWEEN TIME_TO_SEC(inicio) AND (TIME_TO_SEC(termino) + IF(inicio > termino, @umDia, 0)))
OR
  (@terminoSegundos BETWEEN TIME_TO_SEC(inicio) AND (TIME_TO_SEC(termino) + IF(inicio > termino, @umDia, 0)))
OR
  (TIME_TO_SEC(inicio)  BETWEEN @inicioSegundos AND (@terminoSegundos + IF(@inicioSegundos > @terminoSegundos, @umDia, 0)))
OR
  (TIME_TO_SEC(termino) BETWEEN @inicioSegundos AND (@terminoSegundos + IF(@inicioSegundos > @terminoSegundos, @umDia, 0)))
OR
  (TIME_TO_SEC(inicio) = TIME_TO_SEC(termino))

For this example I am considering the following data structure:

CREATE TABLE tempo(
  inicio TIME NOT NULL,
  termino TIME NOT NULL
);

insert into tempo(inicio, termino)
VALUES
('07:00:00' , '09:00:00'),
('11:00:00' , '11:30:00'),
('12:00:00' , '15:35:00'),
('23:50:00' , '03:30:00')

Edit 22/03/2017

I added a special treatment in case there is a full day schedule, for example, start = 10:00:00 and finish = 10:00:00.

I also added a treatment to ignore the opening hours in the enclosure BETWEEN.