Deleting records based on datestamp

Forum Moderators: open

Message Too Old, No Replies

Deleting records based on datestamp

in mysql

wheel

4:38 am on Jul 27, 2007 (gmt 0)

I've got a monster table that I need to trim down because it's screwing up my backups.

Can anyone babysit me through the mysql command I would need in order to:
- figure out the datestamp given a date (the mysql datestamp is some wierd number of seconds since something).
- given that datestamp, select and then delete all records where the value in the datestamp field is < my given datestamp.

In short, I want to delete records earlier than a specific date.

TIA.

roldar

10:04 pm on Jul 29, 2007 (gmt 0)

I'm afraid I can't help you with the first question. I think different versions of mysql handle stamps differently. You should look at the appropriate documentation and then look at your data and make sure that is in fact the format stamps are being stored in.

It could be storing them as : YYYYMMDDHHMMSS or it could be storing them as the number of seconds after the epoch (January 1, 1970), or it could be storing them as one of the above two, but in milliseconds. If you look at a few records you could probably do some quick math and figure out how it is being stored.

Before you go and delete a ton of records, I'd first make a backup. I'd also create a temporary column with a flag value with 'f' as the default. Then you can test your statement using an UPDATE to set the temporary column to 't' in the tuples you plan to delete. If that works as you expect, then you can go ahead and delete them.

i.e.:

UPDATE tableName SET tempBooleanColumn = 't' WHERE stampColumn < (the oldest date you want to keep, in the appropriate format)

If that works correctly, you can go ahead and delete them.

DELETE FROM tableName WHERE tempBooleanColumn = 't'

(Again, make sure you back up your database before you do anything to it)