Delete mysql record older than 30 days

Question

How to delete mysql record older than 30 days? my code will delete all entries that were 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 the mysql table

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

Answer 1

The MySQL table does not store the date, but the unix timestamp (judging by the data provided). To uninstall, follow these steps:

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

Answer 2

Firstly, 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 on the 31st when it is April 1st.

In addition, 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 can do this to get around this problem:

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

Answer 3

Try it.

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

Answer 4

Your SQL is fine, but you are confusing your data types. Just call 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"); 

Answer 5

Perhaps not the most beautiful, but since you seem to save your time as an int. How about subtracting after 30 days and compare with this value:

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

Answer 6

do it like

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

Answer 7

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

Answer 8

Try this sql query:

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

Answer 9

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

Answer 10

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