MySQL Bugs: #16420: Events: timestamps become UTC

Bug #16420	Events: timestamps become UTC
Submitted:	12 Jan 2006 4:12	Modified:	24 Mar 2007 6:06
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.1.6-alpha-debug	OS:	Linux (SUSE 10.0)
Assigned to:	Tomash Brechko	CPU Architecture:	Any
Tags:	rt_q1_2007

Description:
If I say '13:00' I should mean '13:00' in my default
time zone. The server should not convert this to a
UTC time, it is taking away from a user's power to
decide.

How to repeat:

Create an event which should occur every day
at 7am. Notice, by looking at mysql.event,
that the execute_at time is shifted to UTC.
Wait until daylight savings time starts or
finishes. Since this does not affect a UTC
time and the event happens 1 day after the
execute_at, subsequent events should occur
at 6am or 8am local time.

mysql> create event e_31 on schedule at '10:55' do insert into t values (0);
ERROR 1518 (HY000): Activation (AT) time is in the past
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2006-01-12 10:54:42 |
+---------------------+
1 row in set (0.00 sec)

For the time being, this is documented as expected behaviour. (See Bug #17835)

Should the behaviour change, I'll update the docs accordingly - please assign to me when it hits Documenting status.

Quite right, we shouldn't make people think this is intentional or that it's going to last forever, if it's not. See http://lists.mysql.com/commits/4586 - it should be clear now that neither of these things is true. When this bug is fixed, I'll amend the examples, etc., and note that, previous to version 5.1.x, UT was displayed for these timings.

Including into October bug fix plan for the team.

Hello Peter,

If I got your report right than I can't fully agree with it. First of all, we don't have a syntax to say "every day at 9am". Instead, we may say

CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY STARTS '2007-03-24 21:00:00' DO ...

It's natural to think that the event would be run at 9pm local time every day despite the time shifts, because that's how humans understand "days". But please consider another example:

CREATE EVENT e1 ON SCHEDULE EVERY 24 HOUR STARTS '2007-03-24 21:00:00' DO ...

If the time shift will happen on 2007-03-25 02:00:00 (+1 hour), and the next event will run on 2007-03-25 21:00:00, then the time difference between the two events will be only 23 hours. Now I'd say that that's no longer natural (you may consider another interval, like EVERY 83 MINUTE)---I would expect the exact interval. More than that, for negative time shift, 2:30am local time happens twice, so if local time will be used special care should be taken to avoid double execution. So I see using UTC internally as a good idea.

One more note: date/time specified in STARTS, ENDS and AT works naturally over time shift. For instance for MSK time zone:

CREATE EVENT e1 ON SCHEDULE EVERY 1 DAY STARTS '2007-03-24 21:00:00' DO ...
CREATE EVENT e2 ON SCHEDULE EVERY 1 DAY STARTS '2007-03-25 21:00:00' DO ...
SELECT starts FROM INFORMATION_SCHEMA.EVENTS;
+---------------------+
| starts |
+---------------------+
| 2007-03-25 17:00:00 | # MSD: UTC+0400
| 2007-03-24 18:00:00 | # MSK: UTC+0300
+---------------------+

As you may see, future time shift was taken into account, and both events will start at 9:00pm local time.

So exact time/date in STARTS, ENDS and AT works correctly, and EVERY ... is for the relative time difference.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21169

ChangeSet@1.2429, 2007-03-05 21:16:12+03:00, kroki@moonlight.home +29 -0
  BUG#16420: Events: timestamps become UTC
  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
  WL#3698: Events: execution in local time zone
  
  The problem was that local times specified by the user in AT, STARTS
  and ENDS of CREATE EVENT/ALTER EVENT statement were converted to UTC,
  and the original time zone was forgotten.  This way, event scheduler
  couldn't honor Daylight Saving Time shifts, and times shown to the
  user were also in UTC.  Additionally, CREATE EVENT didn't allow times
  in the past, thus preventing straightforward event restoration from
  old backups.
  
  This patch reworks event scheduler time computations, performing them
  in the time zone associated with the event.  Also it allows times to
  be in the past.
  
  The patch adds time_zone column to mysql.event table.
  
  NOTE: The patch is almost final, but the bug#9953 should be pushed
  first.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22123

ChangeSet@1.2468, 2007-03-16 15:54:26+03:00, kroki@moonlight.home +29 -0
  BUG#16420: Events: timestamps become UTC
  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
  WL#3698: Events: execution in local time zone
  
  The problem was that local times specified by the user in AT, STARTS
  and ENDS of CREATE EVENT/ALTER EVENT statement were converted to UTC,
  and the original time zone was forgotten.  This way, event scheduler
  couldn't honor Daylight Saving Time shifts, and times shown to the
  user were also in UTC.  Additionally, CREATE EVENT didn't allow times
  in the past, thus preventing straightforward event restoration from
  old backups.
  
  This patch reworks event scheduler time computations, performing them
  in the time zone associated with the event.  Also it allows times to
  be in the past.
  
  The patch adds time_zone column to mysql.event table.
  
  NOTE: The patch is almost final, but the bug#9953 should be pushed
  first.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22143

ChangeSet@1.2468, 2007-03-16 17:31:07+03:00, kroki@moonlight.home +29 -0
  BUG#16420: Events: timestamps become UTC
  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
  WL#3698: Events: execution in local time zone
  
  The problem was that local times specified by the user in AT, STARTS
  and ENDS of CREATE EVENT/ALTER EVENT statement were converted to UTC,
  and the original time zone was forgotten.  This way, event scheduler
  couldn't honor Daylight Saving Time shifts, and times shown to the
  user were also in UTC.  Additionally, CREATE EVENT didn't allow times
  in the past, thus preventing straightforward event restoration from
  old backups.
  
  This patch reworks event scheduler time computations, performing them
  in the time zone associated with the event.  Also it allows times to
  be in the past.
  
  The patch adds time_zone column to mysql.event table.
  
  NOTE: The patch is almost final, but the bug#9953 should be pushed
  first.

Queued to 5.1-runtime.  The line from change set comment

  NOTE: The patch is almost final, but the bug#9953 should be pushed
  first.

wasn't removed by accident.  Please disregard it.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22168

ChangeSet@1.2471, 2007-03-16 20:50:37+03:00, kroki@moonlight.home +2 -0
  Fix compilation on Windows broken with the push of bug#16420.
  Fix three compilation warnings.

Fixed in 5.1.17

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Noted incompatible change in 5.1.17 changelog; updated discussion of I_S.EVENTS, mysql.event, SHOW EVENTS, CREATE EVENT ... [STARTS|ENDS] ... in 5.1 Manual.

Updated changelog entry:

Scheduled events now use the session time zone that is current when a
CREATE EVENT or ALTER EVENT statement executes is used to interpret
times specified in the event definition (rather than UTC as in
previous releases). The session time zone becomes the event time
zone; that is, the time zone that is used for event scheduling and is
in effect within the event as it executes. Because of this change,
scheduled event metadata now includes time zone information, which
can be seen in the TIME_ZONE column of the INFORMATION_SCHEMA.EVENTS
table and the Time zone column in the output of the SHOW EVENTS
statement. These columns have been added in this release, along with
a time_zone column in the mysql.event table. Due to these changes,
events created in previous versions of MySQL cannot be created,
viewed, or used until mysql.event has been upgraded.
        
For retrievals from INFORMATION_SCHEMA.EVENTS or SHOW EVENTS, times
previously displayed using UTC now use the event time zone.

For information on event time representation:

http://dev.mysql.com/doc/refman/5.1/en/events-metadata.html

See also Bug#47967.