MySQL delete data from a table
user4125170
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: 
Mureinik
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);
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
- Prev: simple example for the creation of a custom directory namer
- Next: recycled twice! Runtime Exception in TypedArray
Related
Related Related
- 1
MySQL delete data from a table
- 2
Delete data from a table by fetching data from another table in mysql
- 3
How to delete mysql data from table at midnight?
- 4
MySql delete from other table
- 5
How to delete all data from MySQL table and populate table with new data
- 6
Trigger to not delete Data from a table
- 7
Data wont delete from table
- 8
how to delete a single value from mysql table
- 9
delete rows from a table using MySQL Scheduler
- 10
delete from MySQL table after matching constraints
- 11
Delete selected row from table in mysql
- 12
Delete consecutive duplicates from a table in mysql
- 13
Delete an entire row from a table, using MySQL
- 14
delete from MySQL table after matching constraints
- 15
Delete selected row from table in mysql
- 16
mysql delete duplicate rows from table
- 17
Delete from MySql table using aggregate functions?
- 18
jQuery onclick delete row from mysql table
- 19
mysql query help for delete entries from table
- 20
How to delete millions of record from mysql table
- 21
How do I SELECT data from a MySQL table and DELETE everything else?
- 22
Mysql DELETE from table using information from another table
- 23
Delete huge amounts of data from huge table
- 24
Delete all data from table but not the columns
- 25
delete a same data from multiple table in php
- 26
Delete Data From Table of Specific Month
- 27
delete existing row from data table
- 28
copy and delete data from one table
- 29
How to update and delete data from table?
Comments