MySQLTutorials

Using MySQL TIMESTAMPDIFF to delete unverified user records

Building a public website with user registration and email verification is typical. It will require users to validate their email by clicking a unique generated URL link.
Sometimes, user registration with pending verification will grow exponentially and needs an automated cleanup of records.

In this example I created a simple MySQL stored procedure to delete users with “pending-email-verification” status for more than five days.

TIMESTAMPDIFF(unit, datetime1, datetime2)

CREATE DEFINER=`dbuser`@`localhost` PROCEDURE `delete_unverified_users`()
BEGIN
	DELETE FROM user WHERE user.status='pending-email-verification' AND TIMESTAMPDIFF(DAY,user.modifiedDateTime,NOW()) > 5;
	SELECT ROW_COUNT() AS 'count';
END