How to Delete a MySQL Record After a Certain Time

How to delete a MySQL record after a certain time

You can try using this condition:

WHERE date < DATE_SUB(NOW(), INTERVAL 7 DAY)

So that the whole SQL script looks like this:

CREATE EVENT delete_event
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
ON COMPLETION PRESERVE

DO BEGIN
      DELETE messages WHERE date < DATE_SUB(NOW(), INTERVAL 7 DAY);
END;

However, on your place I would solve the given problem with a simple cron script. The reasons to do this is simple: it's easier to maintain the code, no ugly SQL workarounds, integrates smoothly with your system.

AUTO-DELETE ROW 1 Day After Inserted (MySQL)

Here's your scripts.

Insert into table1 (id, name, timestamp) values (1, 'test', now())

after insert

Delete from table1 WHERE timestamp < now() - interval 1 day;

Delete a row from a database table after a certain time period

You could use an event eg:

CREATE EVENT deleteEachMonth
ON SCHEDULE EVERY 1 DAY STARTS '2017-05-01 00:00:00'
-- !!! no *END*: will continue until you explicitly drop the event
DO 
DELETE FROM my_table WHERE my_date <  DATE_SUB(curdate(), INTERVAL 1 MONTH);

in your case

CREATE EVENT deleteEachMonth 
ON SCHEDULE EVERY 1 DAY STARTS '2017-05-01 00:00:00' 
DO 
DELETE FROM client WHERE registrationDate < DATE_SUB(curdate(), INTERVAL 1 MONTH) 

Deleting records before a certain date

DELETE FROM table WHERE date < '2011-09-21 08:21:22';

Is it possible to delete records from MySQL based upon the date they were inserted

No, that's not possible.

You need to include a created date in your table definition (and maintain its values) in order to delete rows depending on their creation.

---

Amendment to address to OP's edit about deleting by id:

If you have an incrementing id you can easily delete n rows in descending order. Just ORDER and LIMIT your DELETE query.

E.g., to delete the 100 rows with highest id:

DELETE FROM table ORDER BY id DESC LIMIT 100

Delete records in table after specific period of time in MySQL

multiple ways

• you can opt for setTimeout in js, the problem is when service goes down the last set of records won't be deleted
• you can try scheduling inside mysql itself as well https://dev.mysql.com/doc/refman/8.0/en/events-syntax.html

MYSQL: Delete records from a certain date

Seems you do not have correct date format. The correct mysql format is yyyy-MM-dd

DELETE FROM client_update_history WHERE DATE(date_history) < '2015-11-01'

Do note that if you are using date_history as an index you are better of doing

DELETE FROM client_update_history WHERE date_history < '2015-11-01'

so that this index can be used.

---

Related Topics