How to delete specific records from MySQL by datetime?

Question

PHP

Good afternoon ! )
There is an ACS database where access reports arrive, the problem is that it is necessary to delete records from the database that are identical in time + -20 seconds related to the same employee, while there can be many of them.
For example:
if the mark is before 13 o'clock

1. 2016-08-06 08:59:55
   
2. 2016-08-06 09:00:05
   
3. 2016-08-06 09:00:06
   

And from these records you need to delete everything except the First.
if mark after 13h

1. 2016-08-06 17:59:55
   
2. 2016-08-06 17:00:05
   
3. 2016-08-06 17:00:06
   

And from these records you need to delete everything except the last one.
This is a SQL query displaying repeated entries of one employee in + -20sec with a result image

SELECT lg.lastname,lg.firstname, lg.time 
FROM acc_monitor_log as lg
WHERE lg.lastname in (SELECT lastname FROM acc_monitor_log 
WHERE(time < lg.time and time > DATE_ADD(lg.time,INTERVAL -20 SECOND)) 
OR (time > lg.time AND time < DATE_ADD(lg.time,INTERVAL 20 SECOND)) 
AND lastname = lg.lastname) AND LENGTH(lg.lastname) > 1 
ORDER BY time DESC

Thank you in advance for your attention!

Answer 1

$DATE = date("H:m:s", strtotime($row['date']));
As you can see you can convert date to seconds.
Then it's a matter of two minutes, in a loop we check all the records that match in hours, then in minutes, then in seconds.
And we leave only the first. All
php.net/manual/en/function.date.php