Logic to validate free time in agenda

4

Good evening!

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

In other words, if you already have a schedule from 1:00 p.m. to 2:00 p.m., the other appointments will only be possible if it is before 1:00 p.m. and after 2 p.m.

I tried the following query but failed when the start_date is smaller and the ending date is greater 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 in advance for your attention.

    
asked by anonymous 17.11.2017 / 01:25

2 answers

8

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.

17.11.2017 / 02:00
1

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)
    
17.11.2017 / 02:32