How can delete non verified users after 48 hours except weekend days(Saturday, Sunday) in MySQL-CodePudding

I have query like this

# Delete non verified users after 48 hours
$where = 'is_verified = 0 AND TIMESTAMPDIFF(HOUR, created, now()) >= 48';
$result = $this->common_model->sql_select(TBL_USERS, '*, HOUR(TIMEDIFF(now(), created)) hours, TIMESTAMPDIFF(MINUTE, created, now()) minutes', $where, null);
// then delete the uses

I want to want fetch users from above query except weekend days after 48 hours.

This is my complete script.

please help

Thanks

CodePudding user response：

Do not use CRON. Use built-in Event Scheduler.

Create event procedure

CREATE EVENT remove_old_rows
ON SCHEDULE EVERY 1 DAY
            STARTS '2022-03-11 00:00:00'
DO
DELETE 
FROM tablename
WHERE created_at < CURRENT_DATE - INTERVAL 48 HOUR
  AND WEEKDAY(CURRENT_DATE) < 5;

It will be executed automatically and remove old rows if current weekday is not Saturday or Sunday.

Do not forget to enable Event Scheduler.

CodePudding user response：

you just need to update your where clause,

$where = 'is_verified = 0 AND TIMESTAMPDIFF(HOUR, created, now()) >= 48 AND TRUNC (created) - TRUNC (created, 'IW') NOT IN (5,6) --not sat or sun';

TRUNC (created) - TRUNC (created, 'IW')

returns a value between 0 and 6 inclusive. You can read about trunc as well.

day of the week 
0 | Monday
1 | Tuesday
2 | Wensday
3 | Thursda
4 | Friday
5 | Saturday
6 | Sunday

Note: I have tested this on oracle server. You can test on your sql server and can modify days according to.