Logic to validate free time in agenda

The problem is to organize logic (which is simple).

Basically for one time does not match another, you need only two conditions:

If you want to get Unmatched time - "free" are just these two:

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

OU

• End date of the event A smaller (or equal) than the beginning of event B

What in PHP is:

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

If you want Matching time - "busy" , just use these two more:

• Event start date The lesser (or equal) event than the end of event B

E

• End date of event A greater (or equal) than end of event B

What in PHP is:

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

In either case, you should set >= or simply > depending on whether or not an event that is exactly 9:00 am allowed to "hang out" with an event started 9:00 a.m. (depending on your scenario only specific).

In SQL

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

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. There is also a suggestion to work with native dates in DB instead of dynamically converting in seconds.

Viewing:

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)

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

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

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

There is a simple way to check for an intersection between two date periods. The criteria is as follows:

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.

For example:

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 construct 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 periods that match with a given period, we can do:

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

For periods that do not match , we can do:

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