Welcome to WebmasterWorld Guest from 188.8.131.52
Forum Moderators: open
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.
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.
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)