Help in logic not to overlap a time range already exists in the base

Question

Help in logic not to overlap a time range already exists in the base

Navigation

#1 by (3 votes)

0

Good morning. As suggested by a member of the community, In the post that started this , I have come to ask for collaboration in a logic. I have a table that contains the start-time and dead-time fields. I need a routine that makes it impossible for the user to enter a time range that overlaps with other existing ones, regardless of the day. Any time zone between 24 hours can therefore be entered. 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 because 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 timetable can not overlap an existing schedule.

I would like to create a SQL statement or a Stored Procedure that would return true or false, telling you whether or not to enter the time range sent by the start and end parameters. The times below could be inserted:

   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 note is that you can enter a time range starting at the same time that another one already registered. It can look like this:

   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'

Thank you in advance for your support.

php mysql

asked by anonymous 21.03.2017 / 13:09

1 answer

Element Problem with char and number How to make a button or a checkbox become inaccessible in android studio?

score 3 · Accepted Answer

You can check each time using the BETWEEN clause of the database. To correctly handle dates when the start time is lower than the end, we should add a date range.

See 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 times to seconds with the TIME_TO_SEC function. The treatment of the smaller hours is done by adding another day (in seconds) to the total time.

To correctly check the value, we must have a query for each of the parameters entered, and the final count should return a value that is zeroed. A complete and functional overlay example:

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 03/22/2017

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

I also added a treatment to ignore the start time in the BETWEEN .