LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 06-07-2008, 01:57 PM   #1
Ammad
Member
 
Registered: Apr 2004
Distribution: redhat 9.0, fc4, redhat as 4
Posts: 522

Rep: Reputation: 31
mysql and purge old records


Dear all,

how do i purge all records older than 1 month from mysql database, there are 30 tables in this db. and how do schedule this to occure automatically.
thanks.
 
Old 06-07-2008, 02:12 PM   #2
dkm999
Member
 
Registered: Nov 2006
Location: Seattle, WA
Distribution: Fedora
Posts: 407

Rep: Reputation: 35
MySQL does not automatically keep track of when each record was inserted into its databases. To do this, you will need to include a "date_posted" field in each record, and supply that information when the record is inserted into the database.

Once this is done, it is simple to remove all the old records:
Code:
 delete from mytable where post_date < (curdate()-30)
Of course, you can improve on this by noting that not every month has 30 days, ...

To make this happen automatically, you can write a simple shell script that runs mysql, which in turn will source a script containing your DB commands. Then, use the cron facilities to schedule this script to run at the interval you want.
 
Old 07-13-2011, 03:11 PM   #3
David Triebwasser
LQ Newbie
 
Registered: Jul 2011
Location: Richmond, CA
Posts: 7
Blog Entries: 1

Rep: Reputation: Disabled
I was looking for something like this, I needed to set the INTERVAL value to get one month previous. As below:


select date_sub(curdate(), INTERVAL 1 MONTH);
+---------------------------------------+
| date_sub(curdate(), INTERVAL 1 MONTH) |
+---------------------------------------+
| 2011-06-13 |
+---------------------------------------+
 
Old 10-22-2012, 06:59 AM   #4
akshaymuley
LQ Newbie
 
Registered: Oct 2012
Posts: 1

Rep: Reputation: Disabled
Smile

Quote:
Originally Posted by Ammad View Post
Dear all,

how do i purge all records older than 1 month from mysql database, there are 30 tables in this db. and how do schedule this to occure automatically.
thanks.
Try following code

CREATE EVENT 'AutoDeleteOldNotifications'
ON SCHEDULE EVERY 1 DAY STARTS '00:00:00'

DO BEGIN

DELETE * FROM tableName WHERE datetime< DATE_SUB(NOW(),INTERVAL 30 DAY)

END
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Records are getting added into mysql table automatically prabhatsoni Linux - Software 12 08-10-2007 03:20 AM
selecting records from multiple files in mysql question MRMadhav Programming 7 11-05-2006 05:52 PM
PHP script to retrieve records from MYSQL db and pass them to an external program Julianus Programming 3 08-03-2006 03:09 PM
OOo / MySQL adding records problem pete_bogg Linux - Software 1 04-13-2004 04:31 AM
Import records to MySQL Database from a shell script chrisk5527 Linux - General 12 03-24-2004 10:49 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 07:47 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration