MySQL Bugs: #60550: TIMESTAMPADD throws Incorrect datetime value error over DST boundary

Bug #60550	TIMESTAMPADD throws Incorrect datetime value error over DST boundary
Submitted:	20 Mar 2011 15:50	Modified:	21 Mar 2011 11:20
Reporter:	Greg Thomas	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.5.10	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	DST, timestamp, TIMESTAMPADD

Description:
Under certain circumstances, TIMESTAMPADD can thow an Incorrect datetime value error; for example ...

SET time_zone = 'UTC'
INSERT INTO `test`.`tz_test`( id, date_and_time ) VALUES (2, '2011-03-20 01:30'
)
SET time_zone = 'Europe/London'
UPDATE `test`.`tz_test` SET date_and_time = TIMESTAMPADD( WEEK, 1, date_and_time
 ) WHERE id = 2
--------------
ERROR 1292 (22007) at line 26: Incorrect datetime value: '2011-03-27 01:30:00' f
or column 'date_and_time' at row 1

This is no doubt because of the change to DST that occurs at around this time. Similar errors occur when adding days, hours, etc.

How to repeat:
Run the following script. Note the error;

#
# Script begins
#
USE test;

#
# Create a table
# 
DROP TABLE IF EXISTS tz_test;
CREATE  TABLE `test`.`tz_test` ( `id` INT NOT NULL , `date_and_time` TIMESTAMP NULL , PRIMARY KEY (`id`) );

#
# Set the time zone to UTC (no daylight savings), and create 
# two identical points in time in this table
#
SET time_zone = 'UTC';
INSERT INTO `test`.`tz_test`( id, date_and_time ) VALUES (1, '2011-03-20 01:30' );
INSERT INTO `test`.`tz_test`( id, date_and_time ) VALUES (2, '2011-03-20 01:30' );

#
# Now, add a week to one of them
#
UPDATE `test`.`tz_test` SET date_and_time = TIMESTAMPADD( WEEK, 1, date_and_time ) WHERE id = 1;

#
# Now, do the same, but in a different time zone
#
SET time_zone = 'Europe/London';
UPDATE `test`.`tz_test` SET date_and_time = TIMESTAMPADD( WEEK, 1, date_and_time ) WHERE id = 2;

#
# Script ends
#

Suggested fix:
TIMESTAMP fields are documented as being stored in UTC. 

I would suggest that TIMESTAMPADD function should operate on the UTC value directly, i.e.:
UTC_value = UTC_value + 1 week

Rather than convert the UTC value to the current "local time", add the required time period, and then convert the localtime back to UTC, i.e.
UTC_value = UTCTime(localtime( UTC_value ) + 1 week ))

It's this last convertion from localtime to UTC time that is failing.

This is the only way to get consistent results regardless of the default server timezone or the connections timezone.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read at http://dev.mysql.com/doc/refman/5.1/en/timestamp.html: "TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval." See also when London switch time this year at http://www.timeanddate.com/worldclock/clockchange.html?n=136 So you can not insert 2011-03-27 01:30:00 for London.