nix, shell, perl, php, mysql and mac os x tips and tricks

Friday, March 5, 2010

Select the id of a record that is 6 months old in MySQL

When you need the id of a single record that is 6 months old, and there's a timestamp or date column. Useful if the id is auto-incremented primary key and you need to do a DELETE query in IDs less than returned id (which would be MUCH faster than doing it by a non-indexed timestamp field).
SELECT MAX(id) FROM mail_links WHERE timestamp BETWEEN DATE_SUB(DATE_SUB(NOW(), INTERVAL 6 MONTH), INTERVAL 1 DAY) AND DATE_SUB(NOW(), INTERVAL 6 MONTH);