1

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.

John
  • 1,210
  • 5
  • 23
  • 51
  • Maybe MySQL Workbench localizes the time to your local timezone? – Cobra_Fast Aug 07 '14 at 23:25
  • How to check and disable that? – John Aug 07 '14 at 23:30
  • http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html – double_j Aug 07 '14 at 23:31
  • You can check current values using `SELECT @@global.time_zone, @@session.time_zone;` – double_j Aug 07 '14 at 23:31
  • @double_j It says `SYSTEM` for both after running `SELECT @@global.time_zone, @@session.time_zone;` – John Aug 07 '14 at 23:34
  • @John I'm sort of guessing here but just humor me and check the time on your system? `select now()` see if it's off by 7 hours..? – double_j Aug 07 '14 at 23:51
  • Not really. It's exactly same as the current time here. – John Aug 07 '14 at 23:58
  • @John Have you had this same named event start at 07:00:00 before? You may need to delete if so and then re-create the event. – double_j Aug 08 '14 at 00:16
  • The value 'SYSTEM' indicates that the time zone should be the same as the system time zone. The value can be given as a string indicating an offset from UTC, such as '+10:00' or '-6:00'. – double_j Aug 08 '14 at 00:24
  • @double_j I had the same event before but at that time I didn't define when it should start. Anyways, when I created this new event, I deleted the previous one. Perhaps, I should substract -7:00 from the time. – John Aug 08 '14 at 16:07
  • @John Ya, it is a work around for now. I'm sorry I couldn't help you more – double_j Aug 08 '14 at 16:38

1 Answers1

1

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
F T
  • 88
  • 1
  • 10