MySQL Event scheduler every day start at 12 AM and continue work EVERY 15 MINUTE till 5 AM-CodePudding

CREATE EVENT every_day_1AM_to_5AM_data_insert
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP(CURRENT_DATE)
ENDS TIMESTAMP(CURRENT_DATE) INTERVAL 5 HOUR ON COMPLETION PRESERVE ENABLE
DO Any insert query here;

CodePudding user response：

For what it's worth, when an event starts running its query it continues until the query finishes. Your CREATE EVENT code says to start running the event at CURRENT_DATE (midnight local time on the present date -- a time likely to be already in the past). It says to stop repeating the event at 05:00 on the present day -- also a time likely to be in the past. So, I bet your event never runs.

You may want this to run the event at midnight.

ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE   INTERVAL 1 DAY
ENDS CURRENT_DATE   INTERVAL 1 YEAR
ON COMPLETION PRESERVE

Pro tip Avoid running stuff exactly at midnight. Why? Everybody else does, and your event will have to compete with all the other midnight stuff. Try

STARTS CURRENT_DATE   INTERVAL 1 DAY   INTERVAL 137 SECOND

to run it at some time a few seconds after midnight.

CodePudding user response：

MySQL doesn't support setting a start and end time for every day in the SCHEDULE clause. Instead you should create a stored procedure which gets called by your event and check in there if you are within the desired time window. Like this:

CREATE PROCEDURE my_worker(IN from_time TIME, IN to_time TIME)
BEGIN
  IF CURRENT_TIME() BETWEEN from_time AND to_time THEN

  -- do your work here

  END IF;
END

And here the event:

CREATE EVENT every_day_data_insert
    ON SCHEDULE EVERY '15' MINUTE
    ON COMPLETION PRESERVE ENABLE
    DO 
BEGIN
    CALL `my_worker`('00:30:00', '05:30:00');   --- from 12:30 AM to 5:30 AM
END;