Search code examples
mysqleventstriggersmariadbscheduler

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

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;

Solution

  • 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;