CODEWITHSUNDEEP
mysql
Lamis
Lamis

Reputation: 466

mysql event schedule every sunday

Hello I'm trying to create event schedule on Mysql and set it to work every sunday 00:00

CREATE
    EVENT `reset_column`
    ON SCHEDULE EVERY 1 WEEK STARTS '2013-03-17 03:00:00'
    DO BEGIN

    END */$$
DELIMITER ;

this works but I just want to find another way to tell it to do it exactly at the beginning of every week (e.g ON SCHEDULE EVERY SUNDAY )

Upvotes: 4

Views: 8150

Answers (1)

eggyal
eggyal

Reputation: 125835

ON SCHEDULE
  EVERY 1 WEEK
  STARTS CURRENT_DATE + INTERVAL (6 - WEEKDAY(CURRENT_DATE)) DAY

This works by getting the day the DDL is run(CURRENT_DATE), then adding the days till the next Sunday to that date. It determines the next Sunday by finding that date's WEEKDAY index(0-6), then subtracting it from Sunday's WEEKDAY index(6). Once you set the start date to the next Sunday, you repeat every 1 week.

For the date 2023-04-29, which is a Saturday.

2023-04-29 + INTERVAL (6 - 5) Day = 2023-04-30

Upvotes: 7

Related Questions