Mysql question in two parts

I've got a big piggie Mysql database that I need to flush out, hoping someone can give me the mysql commands to do so.

In one table I have a huge pile of records. Each record has a datestamp. What I want to do is delete all records prior to a certain date.

Seems like I need three parts here (None of which I know how to apply to mysql);
- convert a regular date to the funky datestamp that mysql uses
- select all records where datestamp is less than some date I selected in the first step
- delete all those records.

Any babysitters out there ? The table is like 2 gigs and it's giving my offsite backups a headache. TIA!

 

MySQL can convert from UNIX timestamps itself.

What you're after is something like:
DELETE FROM `table` WHERE `date_column` < FROM_UNIXTIMESTAMP(some_unix_timestamp);

 

I think he wants to convert a regular date to a timestamp.

DELETE FROM `table` WHERE `date_column` < UNIX_TIMESTAMP('2005-12-31 23:59:59');

Code (markup):

 

Thanks folks. I believe that'll get it for me.

I deeply appreciate the assistance.

 

Well, that seemed to have worked - but the file size is still over 2 gigs. Do I have to do some sort of flush command? Restarting mysql didn't change anything.

 

If you have phpMyAdmin (well, even if you don't) you should be able to go into each table and under 'Operations', at the bottom of the screen, you should see 'table maintenance'. I'd read up on defragmenting and optimizing the tables then maybe use one of those...

 

Thank you....that did the trick. A quick google on 'optimize mysql' came up with this:

So I logged into mysql and ran:
OPTIMIZE TABLE tablename
and that cut the size of the file down by over a gig. Not enough yet, but that's a big, big help.