Clever Geek Handbook

MySQL deletes data from table

I have a timestamp value in a column of my table. I need to keep all the data from the past week and delete the remainder data in the table (which is not the last 7 days) How can i do this?

The request I tried is below.

DELETE * FROM EmailMainTable WHERE DATE_FORMAT(timestamp, '%Y-%m-%d %H:%i:%s') > 
DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'), INTERVAL 8 DAY);

NOTE. My filed name is a timestamp and I converted it to bigint

My table structure: enter image description here

+3


source to share


2 answers


Since you are converting timestamps to varchars (using date_format

), they will compare lexicographically , which is not the behavior you want. Just leave the formatting:



DELETE
FROM   EmailMainTable 
WHERE  `timestamp` > DATE_SUB(NOW(), INTERVAL 8 DAY);
+3


source


Finally, I found the answer to my question. This is the answer that worked for me. 
DELETE 
FROM EmailMainTable
WHERE FROM_UNIXTIME(timestamp/1000,"%Y-%m-%d") < DATE_SUB(NOW(), INTERVAL 8 DAY);


+2


source






More articles:


All Articles