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)