By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,191 Members | 1,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,191 IT Pros & Developers. It's quick & easy.

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

DTeCH
P: 23
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:
Expand|Select|Wrap|Line Numbers
  1. 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);
  2.  
  3. SELECT * FROM cu_info.pi_search WHERE DATE_SUB(pPostDate, INTERVAL 700 DAY);
  4.  
  5. SELECT * FROM cu_info.pi_search WHERE DATE_SUB(DATE_FORMAT(pPostDate,'%a, %m %b %Y %T %z'), INTERVAL 700 DAY);
  6.  
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.
Oct 24 '11 #1
Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,366
DATE_FORMAT is used to convert a datetime to a string. Not the other way around. You need to use CONVERT for that.
Expand|Select|Wrap|Line Numbers
  1. SELECT stringDate
  2. FROM someTable
  3. 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.
Oct 24 '11 #2

DTeCH
P: 23
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.
Oct 25 '11 #3

Rabbit
Expert Mod 10K+
P: 12,366
Try substringing out everything from character 6 onwards and dropping the timezone before the convert.
Oct 26 '11 #4

DTeCH
P: 23
Thanks again Rabbit,

I tried your suggestion...
Expand|Select|Wrap|Line Numbers
  1. SELECT pHASH FROM cu_info.wc_search WHERE CONVERT(pPostDate, DATETIME) BETWEEN DATE_SUB(NOW(), INTERVAL 700 DAY) AND NOW();
  2.  
  3. SELECT pHASH FROM cu_info.wc_search WHERE CONVERT(pPostDate, DATETIME) NOT BETWEEN DATE_SUB(NOW(), INTERVAL 700 DAY) AND NOW();
  4.  
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...
Expand|Select|Wrap|Line Numbers
  1. 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...
Expand|Select|Wrap|Line Numbers
  1. 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.
Oct 31 '11 #5

Rabbit
Expert Mod 10K+
P: 12,366
So it sounds like you got it figured out?
Oct 31 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.