Need to delete MySQL records from a table that are older than 700 days

[DTeCH]

Hello all,

I need to delete records from a table in a MySQL database that are more than 700 days old.

There is a column with the time of the record's entry "pPostDate", & the dates are in RFC 2822 Format... the column's format is VARCHAR, not date or datetime.

The date record looks like this... Sun, 08 May 2011 14:21:50 +0000.

I have tried a bunch of crap (MySQL, or databases of any kind are not my strongest point)

Here are a few queries I have tried:

SELECT * FROM cu_info.pi_search WHERE pPostDate BETWEEN DATE_FORMAT(NOW(),'%a, %m %b %Y %T %z') AND DATE_SUB(DATE_FORMAT(NOW(),'%a, %m %b %Y %T %z'), INTERVAL 700 DAY);
 
SELECT * FROM cu_info.pi_search WHERE DATE_SUB(pPostDate, INTERVAL 700 DAY);
 
SELECT * FROM cu_info.pi_search WHERE DATE_SUB(DATE_FORMAT(pPostDate,'%a, %m %b %Y %T %z'), INTERVAL 700 DAY);
 

I know that the queries do not include delete statements... I do not want to mess with that function until I am able to successfully access/return the records to delete.

Any help is greatly appreciated.

[Rabbit]

DATE_FORMAT is used to convert a datetime to a string. Not the other way around. You need to use CONVERT for that.

SELECT stringDate
FROM someTable
WHERE CONVERT(stringDate, DATETIME) BETWEEN DATE_SUB(NOW(), INTERVAL DAYS 700) AND NOW()

You may or may not have to strip off the time zone stuff first.

[DTeCH]

Hey Rabbit, Thanks for your reply.

Your solution looks correct, except that the DateTime format in my case is RFC 2822... eg: Sun, 08 May 2011 14:21:50 +0000, not "2011-06-04 07:9:22"

I've tried converting it on the fly, but I'm not very savy in MySQL, so not very knowledgeable of it's built in functions.

You see, the problem is, the database has over 40 million entries already, but the table that I am using this delete feature on has 6 million, & it's already tied into software, & websites in circulation. So I just can't change the dateTime column to date format, because the Hash of the table's entries will change, & throw off the functionality of everything that depends on it. I want to leave it as is, & maybe add a new column, & have that one be another date format that MySQL functions are used to.

I didn't realize that MySQL didn't understand dates in RFC 2822 when I originally began.

Thanks again for your solution, It works, just not for my case of RFC 2822 Date Format.

[Rabbit]

Try substringing out everything from character 6 onwards and dropping the timezone before the convert.

[DTeCH]

Thanks again Rabbit,

I tried your suggestion...

SELECT pHASH FROM cu_info.wc_search WHERE CONVERT(pPostDate, DATETIME) BETWEEN DATE_SUB(NOW(), INTERVAL 700 DAY) AND NOW();
 
SELECT pHASH FROM cu_info.wc_search WHERE CONVERT(pPostDate, DATETIME) NOT BETWEEN DATE_SUB(NOW(), INTERVAL 700 DAY) AND NOW();
 

however, it wouldn't work no matter how I re-wrote it with those statements. It did return results, but only the ones I didn't want.

So I decided to drop trying to deal with the 2822 formatted datetime column, & added a new one pUnixTimeStamp, & updated it with the row's pPostDate 2822 formatted datetime record converted to Unix TimeStamp (outside of MySQL).

I used the DATE_SUB function you suggested...

DATE_SUB(NOW(), INTERVAL 700 DAY))

But it had to be converted to TimeStamp to be used against pUnixTimeStamp,
You said to use the CONVERT function, so I snooped around, & found another converting function... UNIX_TIMESTAMP()

I placed the above functions inside UNIX_TIMESTAMP(), & it worked.

Here is the final string I now use...

SELECT pHASH FROM cu_info.wc_search WHERE pUnixTimeStamp < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 700 DAY));

ps: The substring removal method was the way to go with the 2822 format datetime, but I didn't know how to do it in MySQL.

[Rabbit]

So it sounds like you got it figured out?