Delete rows where older than 5 Working days

1
votes

I need to create an event. In this event I delete rows of records that are older than 5 WORKING days.

Notice the WORKING days ( Monday, Tuesday, Wednesday, Thursday, Friday )

I got this so far:

CREATE EVENT `delete_news` 
ON SCHEDULE EVERY 1 DAY 
STARTS '2014-03-01 00:00:00' 
ENDS '2014-03-31 00:00:00' 
ON COMPLETION 
NOT PRESERVE 
ENABLE 
DO 
DELETE FROM news WHERE time_created < (NOW() - INTERVAL 5 DAY)

How can I determine the WORKING days in mysql?

Edit:

DELETE FROM news 
WHERE 
    time_created < ( NOW( ) - INTERVAL (
        CASE WEEKDAY(time_created) 
            WHEN 4 THEN 7 
            WHEN 5 THEN 6 
            ELSE 5 
        END) 
     DAY )
2
mysqlsql

2 Answers

2
votes

Use WEEKDAY() or DAYOFWEEK() MySQL functions (depending on your country). Depending on day number subtract 5 or 6 or 7 days

time_created < (NOW() - INTERVAL if(WEEKDAY()=..., 5, else if 6, else if 7) DAY)
0
votes

you should try this not an exactly:

DELETE FROM jmail_history
WHERE date < DATE_SUB(NOW(), INTERVAL 5 DAY)
AND date != 
(
    SELECT MAX(date) FROM jmail_history
    WHERE date < DATE_SUB(NOW(), INTERVAL 5 DAY)
)