Subtract Dates MYSQL

0

I want to delete users with the same name from the table, but only those that are different from the date of the database and the current date (at the time of the select) are less than 5 minutes.

The structure of my database is:

Login | Nome  |        Hora 

    0       joao    2015-05-30 12:05:06
    1       joao    2015-05-30 12:13:06
    2       joao    2015-05-30 12:07:06

As the subtraction of the dates between Login 0 and Login 2 of 2 minutes, it would then delete the most recent, which is login 2.

I'm using the following Query to delete the same names:

DELETE a FROM tabela AS a, tabela AS b WHERE a.nome=b.nome AND a.login < b.login 

I'm using MYSQL, thank you!

    
asked by anonymous 30.05.2015 / 17:47

1 answer

1

Try it out

delete t1 
from tabela t1 
inner join (
   select nome, count(nome)
   from tabela
   group by nome
   having (count(nome) >= 2) 
) as x
on x.nome = t1.nome
where date_add(t1.hora,interval 5 minute) > now();

This query deletes the records with the same name from the table if the difference between the date in the table (Time column) and the current date (curtime ()) is less than 5 minutes.

    
30.05.2015 / 18:14