MySQL Event Scheduler on a specific time everyday

Question

Here s my query CREATE EVENT RESET ON SCHEDULE AT TIMESTAMP DO UPDATE  ndic   students  SET  status     0      How can I update status to  quot 0 quot  at 1 pm every day  What can I use instead of TIMESTAMP

User · Answer

The documentation on CREATE EVENT is quite good, but it takes a while to get it right.

You have two problems, first, making the event recur, second, making it run at 13:00 daily.

This example creates a recurring event.

CREATE EVENT e_hourly
    ON SCHEDULE
      EVERY 1 HOUR
    COMMENT 'Clears out sessions table each hour.'
    DO
      DELETE FROM site_activity.sessions;

When in the command-line MySQL client, you can:

SHOW EVENTS;

This lists each event with its metadata, like if it should run once only, or be recurring.

The second problem: pointing the recurring event to a specific schedule item.

By trying out different kinds of expression, we can come up with something like:

CREATE EVENT IF NOT EXISTS `session_cleaner_event`
ON SCHEDULE
  EVERY 13 DAY_HOUR
  COMMENT 'Clean up sessions at 13:00 daily!'
  DO
    DELETE FROM site_activity.sessions;

User · Answer

Try this   CREATE EVENT event1 ON SCHEDULE EVERY  1  DAY STARTS  2012-04-17 13 00 00  -- should be in the future DO -- your statements END

User · Answer

DROP EVENT IF EXISTS xxxEVENTxxx  CREATE EVENT xxxEVENTxxx   ON SCHEDULE     EVERY 1 DAY     STARTS  TIMESTAMP CURRENT DATE    INTERVAL 1 DAY   INTERVAL 1 HOUR    DO     --process      IMPORTANT -   SET GLOBAL event scheduler   ON

User · Answer

My use case is similar  except that I want a log cleanup event to run at 2am every night   As I said in the comment above  the DAY HOUR doesn t work for me   In my case I don t really mind potentially missing the first day  and  given it is to run at 2am then 2am tomorrow is almost always the next 2am  so I use   CREATE EVENT applog clean event ON SCHEDULE      EVERY 1 DAY     STARTS str to date  date format now      Y m d 0200      Y m d  H i      INTERVAL 1 DAY COMMENT  Test  DO

User · Answer

CREATE EVENT test event 03 ON SCHEDULE EVERY 1 MINUTE STARTS CURRENT TIMESTAMP ENDS CURRENT TIMESTAMP   INTERVAL 1 HOUR DO    INSERT INTO messages message created at     VALUES  Test MySQL recurring Event  NOW

User · Answer

This might be too late for your work  but here is how I did it  I want something run everyday at 1AM - I believe this is similar to what you are doing  Here is how I did it   CREATE EVENT event name   ON SCHEDULE     EVERY 1 DAY     STARTS  TIMESTAMP CURRENT DATE    INTERVAL 1 DAY   INTERVAL 1 HOUR    DO       Your awesome query

[mysql] MySQL Event Scheduler on a specific time everyday

Examples related to mysql

Examples related to events

Examples related to scheduler