Get free hour ranges with SQL only

Question

I am creating an agenda and need to perform a consultation of the interval between scheduled times.

Example:

Tabela: HORARIO_ATENDIMENTO
INICIO: 08:00
FIM:    18:00

Tabela: Agenda

ID: 1
HORA_INICIAL: 08:00
HORA_FINAL:   09:00

ID: 2
HORA_INICIAL: 10:00
HORA_FINAL:   11:00

---

In this scenario we have the schedules FREE: 09:00-10:00 and 11:00-18:00 and it is this information that I would like to view directly in the consultation. I searched here in the stack and did not find anything that gives me a north.

Assuming there is only 1 single day and performing a simple query, we would have:

SELECT id,hora_inicial,hora_final FROM agenda;

Resultado:
1 - 08:00 - 09:00
2 - 10:00 - 11:00

In short, I would like the returned data to be:

Resultado:
1 - 09:00-10:00 
2 - 11:00-18:00

OBS: The end result I already got via code (PHP), but I would like to do this via SQL, because I have doubts about the performance.

If necessary, I can post the code, but since the doubt is not on it, but on how to build the "inverse" SQL let’s say so, I did not find it necessary to post.

Answer 1

You could achieve that result with a trial. Taking your example literally I quickly developed two procedures to help you achieve this result, see: (Mysql)

DROP DATABASE TEST;
CREATE DATABASE TEST;
USE TEST;
CREATE TABLE agenda(ID int ,HORA_INICIAL time ,HORA_FINAL time);
CREATE TABLE horario_atendimento(INICIO time ,FIM time);
CREATE TABLE horario_livre(INICIO time ,FIM time);
select * from agenda;
select * from horario_atendimento;
insert into horario_atendimento(INICIO,FIM) values ('08:00:00','18:00:00');
insert into agenda(ID,HORA_INICIAL,HORA_FINAL) values(1,'08:00:00','09:00:00');
insert into agenda(ID,HORA_INICIAL,HORA_FINAL) values(2,'10:00:00','11:00:00');
truncate table horario_livre;
call TEST.new_procedure();
call TEST.new_procedure2();
select * from TEST.horario_livre;

PROCEDURE1

USE `TEST`;
DROP procedure IF EXISTS `new_procedure`;

DELIMITER $$
USE `TEST`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`()
BEGIN
DECLARE contador INT DEFAULT 1;
    DECLARE limite INT;
    DECLARE idmin INT;
    DECLARE inicio time;
    DECLARE fim time;
    SELECT 
    MIN(ID)
INTO idmin FROM
    TEST.agenda;
    SELECT 
    COUNT(ID)
INTO limite FROM
    TEST.agenda;
WHILE contador<limite DO
            select HORA_FINAL into fim from TEST.agenda where ID =idmin;
            SET idmin = idmin + 1;
            SELECT 
    HORA_INICIAL
INTO inicio FROM
    TEST.agenda
WHERE
    ID = idmin; 
    IF (fim-inicio)<>0 THEN
                INSERT INTO TEST.horario_livre(INICIO ,FIM) values(fim,inicio);
                END IF;
        SET contador = contador + 1;
END WHILE;
END$$

DELIMITER ;

PROCEDURE 2

USE `TEST`;
DROP procedure IF EXISTS `new_procedure2`;

DELIMITER $$
USE `TEST`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure2`()
BEGIN
    DECLARE idmax INT;
    DECLARE fimt time;
    DECLARE fimexpediente time;
SELECT 
    MAX(ID)
INTO idmax FROM
    TEST.agenda;
        SELECT 
    HORA_FINAL
INTO fimt FROM
    TEST.agenda
WHERE
    ID = idmax;
        SELECT 
    FIM
INTO fimexpediente FROM
    TEST.horario_atendimento;   
                INSERT INTO TEST.horario_livre(INICIO ,FIM) values(fimt,fimexpediente);     
END$$

DELIMITER ;