Welcome to the third installment in our series on Database Events! Part 1 outlined the difference between Database Events and Scheduled Tasks, as well as how to configure the Event Scheduler Thread in MySQL. In Part 2, we explored how to create MySQL events using the CREATE EVENT statement. Today's blog will delve deeper into how to schedule MySQL 8 Events - an essential topic that only received a cursory mention last time.

Setting the Execution Interval

Intervals play an important role in the defining of Events. Unless you are creating a one-time event that executes immediately, you have to specify an Interval which specifies some point in the future relative to the current date and time, for example, "two weeks from now". Moreover, in order to have an event reoccur, you have to provide an interval at which to do so, such as "every 6 hours".

Let's start with the event's initial execution time. It consists of the "AT CURRENT_TIMESTAMP" clause, followed by an optional " + INTERVAL interval". The latter part of the AT clause specifies how long to wait before executing. For example, the following event would execute one week after creation:

CREATE EVENT my_event
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 WEEK

The interval portion is based on the intervals accepted by the DATE_ADD() function. These consists of two parts: a quantity and a unit of time. The units keywords are also the same, except that microseconds are not applicable to events.

Here are all the valid Interval unit values and the expected expression argument for each value:

Using the above table as a guide, if we wanted to express minutes and seconds, such as "two minutes and ten seconds", we would write:

CREATE EVENT my_event
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL MINUTE_SECOND '2:10'

Note that:

• Units are always expressed as singular (with no "s").
• In the event definition above, the '2:10' is the expected expression argument, and the MINUTE_SECOND is the interval unit.
• Interval types that combine two different intervals, e.g. minutes and seconds, are known as complex time units.

In cases where there is no interval unit for a specific complex time unit, such as weeks and days, you can combine intervals. For example, AT CURRENT_TIMESTAMP + INTERVAL 3 WEEK + INTERVAL 1 DAY is equivalent to "two weeks and one day from now".

Scheduling Reoccuring Events

Many - if not most events - reoccur according to a specified schedule. The interval at which an event reoccurs is set using the "EVERY interval" clause. Here's the definition for an event that executes every two days:

CREATE EVENT my_event
ON SCHEDULE AT CURRENT_TIMESTAMP
EVERY 2 DAY

In the next blog, we'll learn how to set an event's start and end times.