WL#3698: Events: execution in local time zone
Affects: Server-5.1 — Status: Complete — Priority: Medium
Do time computations, and execute the event, in the "event time zone", a time zone that was associated with the event. Such event time zone is the session time zone that was in effect at event creation time. Currently, all datetime values specified in CREATE EVENT are converted to UTC time zone internally. Because the information about the original time zone is lost, time computations do not honor local Daylight Saving Time changes, thus time intervals like '1 DAY' behave unnaturally (subject of a BUG#16420). The solution is to perform time computations in the time zone associated with the event. Event execution should also happen in the event time zone, so that SELECT NOW() in the event body will return the expected string.
CURRENT STATE ------------- Every event has the following times associated with it (all internal times are in UTC): execute_at datetime of execution for one-shot events interval_value time interval between event runs, measured in interval_type interval_type unit of interval_value starts beginning of the effective period, when the event should be scheduled for execution ends end of effective period created timestamp of the event creation last_altered timestamp of the last event altering last_executed datetime of the last execution 'starts' has the dual meaning: it marks the beginning of the effective period, but also provides the time all subsequent executions will be aligned to. INFORMATION_SCHEMA.EVENTS has the following datetime columns (displayed in the specified time zones): EXECUTE_AT (UTC) STARTS (UTC) ENDS (UTC) CREATED (session time zone) LAST_ALTERED (session time zone) LAST_EXECUTED (UTC) All time computations are done in UTC. Event execution takes place in server time zone. Below is a description of the proposed behavior change. EVENT TIME ZONE --------------- Upon event creation, current session time zone should be stored with the event as the event time zone. Upon event alteration, if the value of STARTS or AT is altered, the event time zone should be updated to the value of the current session time zone. All other time alterations should not affect event time zone. Rationale: event execution is aligned to the STARTS time, AT provides the absolute time of execution. In latter case event time zone will be used during event execution (see below). Currently, the user may use SET @@SESSION.TIME_ZONE= to effectively set event time zone for subsequent CREATE EVENT/ALTER EVENT ... STARTS/ALTER EVENT ... AT. TIME COMPUTATIONS ----------------- All time computations should be performed in event time zone. Rationale: - natural ("symbolic", see below) interval arithmetic in the default case, same result as with SELECT '<datetime>' + INTERVAL ... . - ability to have both "symbolic" and "monotonic" intervals. For instance, if we say 'EVERY 2 HOUR STARTS "... 0:00:00"' for the event in the time zone that has Daylight Saving Time rules, then we'll get "symbolic" interval: the event will be executed at 0:00, 2:00, 4:00, ..., 22:00 according to the event time zone, regardless of any DST changes (i.e it will be executed at 4:00, even if it's 1 or 3 hours past 2:00 because of DST time shift). But if we say the same for the event in the UTC time zone (or any other time zone not having DST), we'll get a "monotonic" interval: the event will be executed exactly every 2 hours. Strictly speaking, all intervals are "symbolic" in their time zone, but may look "monotonic" or not when viewed from another time zone. - straightforward support of mixed interval types where one part is of possibly varying length, like DAY_HOUR. The behavior will simply be defined by the effective type of the interval, "monotonic" or "symbolic" (in any case 1 DAY == 24 HOUR according to event time zone). EVENT EXECUTION --------------- Event execution should happen in the context of the event time zone, i.e SELECT NOW() should return the same string as it would return if the user who created the event would execute it manually instead at the desired time. NOTE: one may argue the inconsistency: if I create an event in one time zone, and then do ALTER EVENT in another time zone, it's clear that new times (if given) are in the current new time zone. But why the body should be executed in the new time zone, and not the original one? INFORMATION_SCHEMA.EVENTS ------------------------- A column TIME_ZONE CHAR(64) DEFAULT 'SYSTEM' should be added, holding the name of the event time zone, or the offset in the format +/-HH:MM. Furthermore, all datetimes previously displayed in UTC should be displayed in their event's time zone. Such information from I_S.EVENTS will be sufficient to recreate the event. As a consequence, I_S.EVENTS will display local times for events that were created in the current session time zone (BUG#16420). Rationale: it's also possible to display all times in the current session time zone (instead of displaying times of the events each in its own time zone as proposed). However, that may bring in some confusion: imagine we have two events, e1 and e2, such that e1 was created with STARTS '2007-03-24 0:00:00' in UTC time zone, and e2 was created with STARTS '2007-03-24 3:00' in MSK(UTC+3) time zone. The interval for both events is 2 DAY. Now the user in UTC time zone does the select from I_S.EVENTS, and gets: NAME STARTS INTERVAL e1 2007-03-24 0:00 2 DAY e2 2007-03-24 0:00 2 DAY This is correct, as the events indeed appointed to start at the same datetime. However, the user may further assume that eventually both events will start at 2007-03-26 0:00 UTC, which is wrong for e2 because of DST time shift in between. Having clear statement that all times are in their event's time zones will invalidate such straightforward comparison right from the start. CREATED and LAST_ALTERED are left in session time zone because that's how they are treated in other places (and they more belong to the information about the event, not the event itself). SHOW EVENTS ----------- There will be new column TIME_ZONE in SHOW EVENTS. All datetimes will be shown in the same time zones as described in INFORMATION_SCHEMA.EVENTS section above. SHOW CREATE EVENT ----------------- There will be new column TIME_ZONE in SHOW CREATE EVENT. Currently, SHOW CREATE EVENT does not show STARTS and ENDS datetimes, but does show AT datetime. This will be fixed. All datetimes will be shown in the time zone specified by TIME_ZONE column. I.e. if current session time zone equals to TIME_ZONE, then statement shown by SHOW CREATE EVENT is ready for cut-n-paste (but see the following section). BACKUP AND RESTORE ------------------ Currently CREATE EVENT does not accept AT, STARTS, ENDS datetimes in the past. This prevents straightforward event restoration using the statement shown with SHOW CREATE EVENT, or reconstructed from INFORMATION_SCHEMA.EVENTS. See also: BUG#26429 "SHOW CREATE EVENT is incorrect for an event that STARTS NOW() BUG#26431 "Impossible to re-create an event from backup if its STARTS clause is in the past" Once backup and restore are properly supported in the server, the functionality implemented in scope of this worklog entry will be also supported automatically by MySQL backup and restore facilities. One consideration has to be taken into account, however: as currently the session time zone is a part of event definition, this time zone has to be preserved by mysqlbackup progam. This can be done by optional output of SET @@time_zone statement that will temporarily activate event time zone before event definition. DAYLIGHT SAVING TIME -------------------- Because of DST certain local times (like 2:30am) may happen twice, or not happen at all. Fortunately, system functions are aware of that. For instance, in MSK time zone: Forward 1:59:59->3:00:00 shift: SELECT UNIX_TIMESTAMP('2007-03-25 1:59:59'); => 1174777199 SELECT UNIX_TIMESTAMP('2007-03-25 2:00:00'); => 1174777200 # non-existing time SELECT UNIX_TIMESTAMP('2007-03-25 2:30:00'); => 1174777200 # same as above SELECT UNIX_TIMESTAMP('2007-03-25 3:00:00'); => 1174777200 SELECT UNIX_TIMESTAMP('2007-03-25 3:00:01'); => 1174777201 Backward 2:59:59->2:00:00 shift: SELECT UNIX_TIMESTAMP('2007-10-28 1:59:59'); => 1193522399 SELECT UNIX_TIMESTAMP('2007-10-28 2:00:00'); => 1193526000 # above + 1 hour shift + 1 sec SELECT UNIX_TIMESTAMP('2007-10-28 2:30:00'); => 1193527800 # natural, above + 30 mins Because events scheduler operates on times in UTC internally, for forward DST shifts the events appointed to the skipped time will be automatically executed right after the shift has happened (because that's how local->UTC conversion works). For backward DST shifts, the events will be executed only once (even if local 2:30 happens twice), because locale->UTC conversion will map "both" 2:30 to the one UTC time. So the problem with missing or double execution will be naturally solved. INCOMPATIBLE CHANGES -------------------- Here is the summary of incompatible changes mentioned above: - new column in mysql.event system table. - new column in INFORMATION_SCHEMA.EVENTS. - the following times in I_S.EVENTS: EXECUTE_AT STARTS ENDS LAST_EXECUTED are displayed in corresponding event's time zones. - time computations performed in event time zone. - execution performed in event time zone. Hint For Documentation ---------------------- Workaround for Daylight Saving Time shift with short intervals Short event intervals may not work in the way that you hope, after a shift to or from daylight saving time. For example, an EVERY 1 SECOND event will occur at 02:59:59 on the day of the shift, and then occur again at 03:00:00 -- but that's 3600 seconds later, because the clock went backward. To make sure that your event will really occur every second, you will have to temporarily switch from your current time zone to a fixed offset from UTC, then create the event, then switch back to your current time zone again. Here's how. This function will check your current time zone and return a fixed offset from UTC in the form '+hh:mm' or '-hh:mm'. DELIMITER // CREATE FUNCTION f_offset () RETURNS CHAR(6) BEGIN DECLARE offset_seconds INT; SET offset_seconds = TIMESTAMPDIFF(SECOND,UTC_TIMESTAMP,CURRENT_TIMESTAMP); IF offset_seconds < 0 THEN RETURN LEFT(SEC_TO_TIME(offset_seconds),6); ELSE RETURN LEFT(CONCAT('+',SEC_TO_TIME(offset_seconds)),6); END IF; END// DELIMITER : Using the above function, make a fixed-offset event as follows: SET @saved_time_zone = @@time_zone; /* "Push" current time zone */ SET TIME_ZONE = f_offset(); /* Switch to fixed-offset zone */ CREATE EVENT ... ON SCHEDULE EVERY (short interval) DO ...; SET TIME_ZONE = @saved_time_zone; /* "Pop" current time zone */ You may need to use the above method for events which repeat in intervals shorter than one day, that is, SECOND or MINUTE or HOUR events. If you use it, any references to NOW() or CURRENT_TIME or CURRENT_TIMESTAMP or CURDATE() or CURTIME() or FROM_UNIXTIME functions will operate in the time zone offset obtained from the f_offset() call. To use a different time zone for the execution of the event body, do SET TIME_ZONE= 'time zone'; as the first statement of the body." E-MAIL DISCUSSIONS ------------------ https://intranet.mysql.com/secure/mailarchive/mail.php?folder=214&mail=239 https://intranet.mysql.com/secure/mailarchive/mail.php?folder=214&mail=240
Design is part of BUG#16420; no further LLD needed.
Copyright (c) 2000, 2017, Oracle Corporation and/or its affiliates. All rights reserved.