Search code examples
mysqlmysql-workbenchmysql-event

SELECT * FROM mysql.event showing different start time

While creating an event in mysql, I have schedule defined as follows:

 ON SCHEDULE EVERY 1 DAY STARTS '2014-08-08 00:00:00'

However, when I ran the following query:

SELECT * FROM mysql.event;

I saw the following output:

enter image description here

I am wondering why the starts time is shown as '2014-08-08 07:00:00' instead of '2014-08-08 00:00:00' which I defined at the time of creating an event. Please let me know.

Solution

  • https://dev.mysql.com/doc/refman/5.5/en/events-metadata.html

    For representation of event information in the mysql.event table, the execute_at, starts, and ends times are converted to UTC and stored along with the event time zone. This enables event execution to proceed as defined regardless of any subsequent changes to the server time zone or daylight saving time effects. The last_executed time is also stored in UTC.

    If you select information from mysql.event, the times just mentioned are retrieved as UTC values. These times can also be obtained by selecting from the INFORMATION_SCHEMA.EVENTS table or from SHOW EVENTS, but they are reported as ETZ values. Other times available from these sources indicate when an event was created or last altered; these are displayed as STZ values. The following table summarizes representation of event times.

    Value   mysql.event INFORMATION_SCHEMA.EVENTS   SHOW EVENTS
Execute at  UTC ETZ ETZ
Starts  UTC ETZ ETZ
Ends    UTC ETZ ETZ
Last executed   UTC ETZ n/a
Created STZ STZ n/a
Last altered    STZ STZ n/a