Delete based on timestamp grouping

Question

I am trying to delete from MySql table based on a timestamp grouping. Similar to what I have in a GROUP BY but delete the records based on the similar timestamp grouping. Is this possible? Delete all records that fall withing a 10 min range of the supplied timestamp.

DELETE c.* FROM commissions c 
LEFT JOIN comm_manuf_link ml ON (c.id = ml.comm_id)
LEFT JOIN manuf m ON (ml.manuf_id = m.id)
WHERE m.id = '21'
AND c.datestamp = UNIX_TIMESTAMP('2023-07-20 10:53:17') / 600;

The SELECT GROUP BY just for example purposes:

GROUP BY UNIX_TIMESTAMP(c.datestamp) DIV 600

Answer 1

The condition should be:

c.datestamp >= UNIX_TIMESTAMP('2023-07-20 10:53:17') - 600
AND
c.datestamp <= UNIX_TIMESTAMP('2023-07-20 10:53:17') + 600

or:

c.datestamp BETWEEN UNIX_TIMESTAMP('2023-07-20 10:53:17') - 600 AND UNIX_TIMESTAMP('2023-07-20 10:53:17') + 600

or:

ABS(c.datestamp - UNIX_TIMESTAMP('2023-07-20 10:53:17')) <= 600

Also, the condition m.id = '21' in the WHERE clause changes your LEFT joins to INNER joins because it filters out all the unmatched rows.

Also, there is no point for the join to manuf since the value of the column id of manuf can be retrieved from the column manuf_id of the table comm_manuf_link.

Write the query like this:

DELETE c.* 
FROM commissions c INNER JOIN comm_manuf_link ml 
ON c.id = ml.comm_id
WHERE ml.manuf_id = '21'
  AND c.datestamp >= UNIX_TIMESTAMP('2023-07-20 10:53:17') - 600
  AND c.datestamp <= UNIX_TIMESTAMP('2023-07-20 10:53:17') + 600;