CODEWITHSUNDEEP
javamysqltimestampbigint
user4125170
user4125170

Reputation:

MySQL delete data from a table

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: enter image description here

Upvotes: 3

Views: 163

Answers (2)

user4125170
user4125170

Reputation:

Finally I found the answer for my own 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);

Upvotes: 2

Mureinik
Mureinik

Reputation: 311978

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);

Upvotes: 3

Related Questions