CODEWITHSUNDEEP
phpmysqlsql-delete
fish man
fish man

Reputation: 2720

Delete mysql record older than 30 days

How to delete mysql record older than 30 days? my code will delete all the records even which are inserted yesterday. 

require('../conn_db.php');
mysql_select_db("my_news",$conn);
mysql_query("SET NAMES utf8");
mysql_query("DELETE FROM my_news WHERE date < DATE_SUB(NOW(), INTERVAL 1 MONTH)");
mysql_close("my_news");

And mysql table

date int(10)
1321095600
1322107200
...
1328288400
1328290440

Upvotes: 6

Views: 33079

Answers (10)

heyanshukla
heyanshukla

Reputation: 669

Try to use date_interval_create_from_date_string('1 MONTH') instead of INTERVAL 1 MONTH in the second parameter of DATE_SUB().

Upvotes: 0

professorsloth
professorsloth

Reputation: 339

First off, if you really want to delete records older than 30 days, use INTERVAL 30 DAY instead, when you use INTERVAL 1 MONTH you will delete records added on Mars 31st, when it's April 1st.

Also, your date-column is of type int, and DATE_SUB() will return a date in this format YYYY-MM-DD HH:MM:SS, so they are not comparable. You could do this to work around that problem:

DELETE FROM my_news WHERE date < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))

Upvotes: 11

Sarath Tomy
Sarath Tomy

Reputation: 504

mysql_query("DELETE FROM my_news WHERE date < DATE_SUB(CURDATE(), INTERVAL 1 MONTH)");

Upvotes: 0

Brendan Bullen
Brendan Bullen

Reputation: 11819

Your SQL is fine but you have confused your datatypes. Just make a call to UNIX_TIMESTAMP(date): http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp

require('../conn_db.php');
mysql_select_db("my_news",$conn);
mysql_query("SET NAMES utf8");
mysql_query("DELETE FROM my_news WHERE date < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 MONTH))");
mysql_close("my_news");

Upvotes: 2

Java
Java

Reputation: 2489

Try this . 

mysql_query("DELETE FROM my_news WHERE date < DATE_SUB(NOW(), INTERVAL 30 DAY)");

Upvotes: 3

MakuraYami
MakuraYami

Reputation: 3428

$expiretime = time() - 2592000; //1 * 60 * 60 * 24 * 30
mysql_query("DELETE FROM my_news WHERE date < ".$expiretime);

Upvotes: 1

Somnath Muluk
Somnath Muluk

Reputation: 57846

Try this sql query:

DELETE FROM my_news WHERE DATEDIFF(NOW() ,date )>30;

Upvotes: 0

Christofer Eliasson
Christofer Eliasson

Reputation: 33875

Maybe not the prettiest, but since you seems to store your time as an int. How about just subtracting 30 days from now, and compare to that value:

DELETE 
FROM my_news 
WHERE `date` < (UNIX_TIMESTAMP() - (60 * 60 * 24 * 30))

Upvotes: 1

Mithun Satheesh
Mithun Satheesh

Reputation: 27845

do it like

 $time_ago = strtotime("-30 day");
 mysql_query("DELETE FROM my_news WHERE date < $time_ago");

Upvotes: 1

clops
clops

Reputation: 5255

Your MySQL Table does not store a date, but rather a unix timestamp (judging from the data you have provided). To delete do the following:

mysql_query("DELETE FROM my_news WHERE date < ".strtotime('-1 month'));

Upvotes: 3

Related Questions