Subtract MYSQL Dates

Question

I want to delete from the table users with the same name, but only those that the difference of the database date and the current date (at the time of select) are for example, 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 delete the most recent one, which is login 2.

I am using the following Query to delete equal names :

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

I am using MYSQL ,thank you !

Answer 1

Try it like this

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 records with the same name from the table if the difference between the date in the table (Time column) and the current date (like()) is less than 5 minutes.