Query to find duplicate by DATETIME (MYSQL)

Question

Good morning to all. I need help to better understand how to apply a query that I’m developing.

I would need to formulate a SELECT to seek certain records within a period involved (DATETIME).

On DB:

# sala_c, date_start, date_end, name
'AUD_BARATA', '2020-01-25 14:00:00', '2020-01-25 15:00:00', 'TESTE AVISO APÓS GRAVAR'
'SAL_EVENTOS', '2020-01-25 15:00:00', '2020-01-25 16:00:00', 'DESCRIÇÃO EVENTO'
'SAL_SALAGRUPO', '2020-01-28 13:00:00', '2020-01-28 14:00:00', 'TESTE GRUPO TESTE'
'SAL_SALAGRUPO', '2020-01-28 13:00:00', '2020-01-28 14:00:00', 'EVENTO REPETIR - DUPLICATE'
'SAL_SALAGRUPO', '2020-01-28 13:30:00', '2020-01-28 14:30:00', 'REPETIR - ANTERIOR BETWEEN'
'SAL_SALAGRUPO', '2020-01-31 11:00:00', '2020-01-31 12:00:00', 'TESTE REPETIDO 2'

There are 3 records on the date of day 28/01, but 2 of them are with the same time and 1 half hour late. So I made this query to locate records that have the same room, day and time conflicted:

SELECT e.date_start, e.name, ec.sala_c
FROM  fp_events e, fp_events_cstm ec
WHERE  ec.id_c = e.id
AND ( e.date_start, e.date_end, ec.sala_c ) IN
        ( select 
             e.date_start, e.date_end, ec.sala_c 
          from fp_events e, fp_events_cstm ec
          where  ec.id_c = e.id
           and e.date_start between e.date_start and e.date_end
              group by e.date_start, e.date_end, ec.sala_c
        having count(*) > 1 )
        and e.deleted = 0

However the result is this:

# date_start, name, sala_c
'2020-01-28 13:00:00', 'TESTE GRUPO TESTE', 'SAL_SALAGRUPO'
'2020-01-28 13:00:00', 'EVENTO REPETIR - DUPLICATE', 'SAL_SALAGRUPO'

Man SELECT is not considering what is 30 min late, although the day and the room are the same. I need a way for him to consider the time conflict involved from this very date. I do not know if I was very clear. But the idea is to find conflicts of the day and considering initial time and final time.

Answer 1

It’s an interesting problem of intervening mathematics. One thing you’ll need is a primary key, a single ID per line, so a schedule doesn’t conflict with itself:

mysql> select * from teste;
+---------------+---------------------+---------------------+----------------------------+----+
| salac         | date_start          | date_end            | name                       | id |
+---------------+---------------------+---------------------+----------------------------+----+
| AUD_BARATA    | 2020-01-25 14:00:00 | 2020-01-25 15:00:00 | TESTE AVISO APÓS GRAVAR    |  1 |
| SAL_EVENTOS   | 2020-01-25 15:00:00 | 2020-01-25 16:00:00 | DESCRIÇÃO EVENTO           |  2 |
| SAL_SALAGRUPO | 2020-01-28 13:00:00 | 2020-01-28 14:00:00 | TESTE GRUPO TESTE          |  3 |
| SAL_SALAGRUPO | 2020-01-28 13:00:00 | 2020-01-28 14:00:00 | EVENTO REPETIR - DUPLICATE |  4 |
| SAL_SALAGRUPO | 2020-01-28 13:30:00 | 2020-01-28 14:30:00 | REPETIR - ANTERIOR BETWEEN |  5 |
| SAL_SALAGRUPO | 2020-01-31 11:00:00 | 2020-01-31 12:00:00 | TESTE REPETIDO 2           |  6 |
+---------------+---------------------+---------------------+----------------------------+----+

The following consultation seemed to work:

select * from teste a inner join teste b
      on a.id < b.id and 
         a.date_start < b.date_end and
         a.date_end > b.date_start;

I used a.id < b.id, it could be a.id <> b.id, but then the query would list the duplicate conflicts (A conflicting with B and B conflicting with A).

Perhaps it was still lacking to test if a.salac = b.salac, it was not clear in the question whether commitments in different rooms conflict or not.

In interval mathematics, to test whether two ordered intervals (A,B) and (C,D) overlap, the test is

 A < D e B > C