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


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:

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




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




All Articles