WL#3698: Events: execution in local time zone

Affects: Server-5.1 — Status: Complete

Description
High Level Architecture
Low Level Design

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 '' + 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.