Logic to validate free schedules on schedule

Question

good night!

I’m stuck for a few days in the following situation, I’m not able to parameterize or create an expression that validates interval between dates, which works like this:

that is, if you already have a schedule from 13:00 to 14:00 the other schedules will only be possible if it is before 13:00 and after 14:00

I tried the seguine query but it fails when the start date is smaller and the end date larger than the one in the database as shown in the image.

SELECT * FROM agenda WHERE livro_idlivro = 4 AND ((UNIX_TIMESTAMP('2017-11-15 11:59:00') >= UNIX_TIMESTAMP(data_entrada) AND UNIX_TIMESTAMP('2017-11-15 11:59:00') <UNIX_TIMESTAMP(data_saida))) OR ((UNIX_TIMESTAMP('2017-11-15 17:59:00') >= UNIX_TIMESTAMP(data_entrada) AND UNIX_TIMESTAMP('2017-11-15 17:59:00') <UNIX_TIMESTAMP(data_saida))) AND livro_idlivro =4

Thank you for your attention.

Answer 1

The problem is organizing logic (which is simple).

Basically for one time not to coincide with another, you need only two conditions:

If you want to get Non coincident hours - "free" are just these two:

• Event start date A greater (or equal to) than the end of event B

OR

• Event end date A less (or equal to) than event start B

Which in PHP is:

$livre = $inicio_pretendido >= $final_existente || $final_pretendido <= $inicio_existente

If you want Time coincident - "busy", only use these other two:

• Event start date A less (or equal to) than the end of event B

And

• Event end date A greater (or equal to) than the end of event B

Which in PHP is:

$ocupado = $inicio_pretendido <= $final_existente && $final_pretendido >= $inicio_existente

In both cases, you must adjust >= or simply > depending on whether or not an event finished exactly 9am can "live" with an event started 9am (it only depends on your specific scenario).

In SQL

Example of query to find free, but with "coexistence" at the time of the "turn":

SELECT * FROM tabela WHERE
   ( data1_inicio >= data2_fim OR data1_fim <= data2_inicio )
   AND id_livro = 4
;

Note the ( ) in the part that tests the dates. It is also suggested to work with native dates in DB, instead of converting dynamically in seconds.

Visualizing:

Based on these 6 events:

           AAAAAAAAAAAAAAAAAAA
           |                 |
1 BBBBBBBBBBBB               |           Ocupado
2          |  BBBBBBBBBBBBBB |           Ocupado
3          |            BBBBBBBBBBBBB    Ocupado
4 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB Ocupado
5 BBBB     |                 |           Livre
6          |                 | BBBBBBBBB Livre

We have this truth table with all possible comparisons:

1 A ini < B ini  A ini < B fim  A fim > B ini  A fim < B fim  Ocupado
2 A ini > B ini  A ini < B fim  A fim > B ini  A fim < B fim  Ocupado
3 A ini > B ini  A ini < B fim  A fim > B ini  A fim > B fim  Ocupado
4 A ini < B ini  A ini < B fim  A fim > B ini  A fim > B fim  Ocupado
5 A ini < B ini  A ini < B fim  A fim < B ini  A fim < B fim  Livre
6 A ini > B ini  A ini > B fim  A fim > B ini  A fim > B fim  Livre
  Condicao 1     Condicao 2     Condicao 3     Condicao 4
   (inutil)        (util)         (util)        (inutil)

• Note that conditions 1 and 4 do not help us at all;

• Conditions 2 and 3 are sufficient to determine what is free and what is occupied.

Answer 2

To check if a date range is free, you need to check that the interval does not match the other recorded intervals.

There is a simple way to check if there is an intersection between two date periods. The criterion is the following:

SELECT *
  FROM Tabela
 WHERE Data_Ini_Periodo1 <= Data_Fim_Periodo2 
   AND Data_Fim_Periodo1 >= Data_Ini_Periodo2

The condition will be true whenever there is an overlap between Period1 and Period2.

In the example below, the condition will be true for records 2 and 3:

Data_Ini_Periodo1 = 15/11/2017 11:00
Data_Fim_Periodo1 = 15/11/2017 13:59

   Data_Ini_Periodo2   Data_Fim_Periodo2  Resultado_Condicao_Intersecao
1  15/11/2017 09:00    15/11/2017 09:59   falso
2  15/11/2017 10:00    15/11/2017 11:59   verdadeiro
3  15/11/2017 12:00    15/11/2017 12:59   verdadeiro
4  15/11/2017 15:00    15/11/2017 15:59   falso

To check if a period is free or busy, we can build the following query:

select case when exists(select * 
                          from agenda
                         where @data_inicio <= data_saida
                           and @data_fim >= data_entrada)
            then 'Já existe agendamento para esse horário'
            else 'Período livre' end

To get the matching periods with a certain period, we can do:

select * 
  from agenda
 where @data_inicio <= data_saida
   and @data_fim >= data_entrada

To get the periods that do not match, we can do:

select * 
  from agenda
 where not(@data_inicio <= data_saida
           and @data_fim >= data_entrada)