Increment a value for entire database

So we have a database. Every so often we'll add a new data set and the oldest dataset should be removed.

Say the DB is Early June: 18 records, Late June: 15 records, Aug: 23 records. When we add the next records we want to remove the Early June records. However adding new data isn't totally regular. My second thought is to have an "oldness" field for each record and before adding a new data set, increment all of the "oldness" fields by 1, then removing all the data that has "oldness" of 3.

I'm worried that this is not the optimal way to do this, but I'm not sure it matters since it's a fairly small database that is开发者_运维问答 updated infrequently. That said, I'm certainly open to suggestions for a better algorithm to accomplish this.

I'm assuming your data is stored such that it has a timestamp (date) column for each report, and that you always want to remove data that is x (in this case, 3) months old. If so, you might think about using mysql's DATEDIFF function.

For example, it might look something like this:

DELETE from table1 WHERE datediff(CURRENT_DATE(), datecol) > 89

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_datediff

Is it always by month?

UPDATE table SET status = "oldness" WHERE date_inserted <= NOW() - INTERVAL 3 MONTH

With a little help from user937146 I think I've got the solution. Basically

$query = "SELECT MIN(timestamp) FROM new_items WHERE type = 'Book' ";

Then it's just a matter of taking that timestamp, adding an hour (since the data can take more than a second to upload, probably one minute would be sufficient) and then deleting everything from the database older than that timestamp.