CODEWITHSUNDEEP
mysqlsqlevents
pandoragami
pandoragami

Reputation: 5585

MySQL CurDate() format to use MySQL EVENT for deleting (cleaning table) data after a day?

I have a MySQL table named registered where one of my rows named time is of type timestamp and I wanted to add an EVENT where all the entries in this table are deleted if they are older than one day. I have this so far...

CREATE EVENT delete_registration_data
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM `registered` where time < DATE_SUB(CurDate(), INTERVAL 1 DAY);

What I noticed however is that the timestamp datatype is made up of the CURDATE() and CURTIME() e.g. 2008-11-11 12:45:34. Would this cause a problem for the EVENT handler to delete from the table?

Upvotes: 1

Views: 412

Answers (1)

undefined_variable
undefined_variable

Reputation: 6218

CREATE EVENT delete_registration_data
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM `registered` where date(time) < DATE_SUB(CurDate(), INTERVAL 1 DAY);

Upvotes: 2

Related Questions