MySQL delete data from a table

Question

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

The query that I tried is given 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 timestamp and i converted it to bigint

My table's structure:

Answer 1

Since you're converting the timestamps to varchars (using date_format), they will be compares lexicographically, which isn't the behavior you want. Just drop the formatting:

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