Welcome to WebmasterWorld Guest from 34.204.36.101

Forum Moderators: open

Message Too Old, No Replies

Deleting records based on datestamp

in mysql

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

Senior Member

WebmasterWorld Senior Member wheel is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Feb 11, 2003
posts:5072
votes: 12


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.

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

Full Member

10+ Year Member

joined:Apr 21, 2004
posts:306
votes: 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)