CODEWITHSUNDEEP
phpmysqldatabase
Manoj Kumar
Manoj Kumar

Reputation: 646

Keep 1 Week Data in database

I have some data in my database this data updates everyday i want to keep only 1 week data in my database i dont want data older than 1 week

can anyone tell me how to delete all the data except the one week data from database

Upvotes: 1

Views: 667

Answers (4)

Silambarasan
Silambarasan

Reputation: 787

You have 2 options.

1.Event Scheduler

First create table and create event for perform delete query for delete 1 weeks ago record.

CREATE EVENT `ex1` ON SCHEDULE
EVERY 1 DAY
ON COMPLETION NOT PRESERVE
ENABLE
COMMENT ''
DO BEGIN
DELETE FROM table WHERE DATEDIFF(NOW(),TimeStampColumn) > 7

END
//for 3 weeks

Note : Old versions of mysql don't have event scheduler

2. Create cron job

file1.sql

DELETE FROM table WHERE DATEDIFF(NOW(),TimeStampColumn) > 7

schedule this command:

mysql -uusername -ppassword < /path/to/file1.sql

Upvotes: 0

Jay D.
Jay D.

Reputation: 672

Set up a cron job to check when data is older than a week. Then delete using a where clause. 

DELETE FROM table WHERE DATEDIFF(NOW(),TimeStampColumn) > 7

Upvotes: 3

Erik
Erik

Reputation: 222

Easiest way: make sure the entry date/time is recorded for all data in the db and run a cron to periodically remove all data older than a week.

Upvotes: 0

sarnold
sarnold

Reputation: 104070

The RRDtool database format and bindings provide easy access to round-robin database capabilities in a variety of languages; RRD is the de-facto standard for many system monitoring tools.

Upvotes: 0

Related Questions