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
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;