Bug #40329 TIMESTAMPADD does not take in to account Daylight Savings Time.
Submitted: 25 Oct 2008 18:43 Modified: 1 Dec 2008 16:43
Reporter: Greg Thomas Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:mysqld-nt Ver 5.0.67-community-nt for Win32 on ia32, 5.0, 5.1, 6.0 OS:Any
Assigned to: Paul DuBois CPU Architecture:Any
Tags: DST, timestamp, TIMESTAMPADD

[25 Oct 2008 18:43] Greg Thomas
When using TIMESTAMPADD, changes to daylight savings time are not taken in to account. For example, at 1am on 30 March 2008, the UK enters DST, jumping from 00:01:00 to 00:02:00. Hence, the time '2008-03-30 01:30:00' is an invalid timestamp in the UK/London timezone.

Yes, if you use TIMESTAMPADD to add one hour to '2008-03-30 00:30:00' you get '2008-03-30 01:30:00'.

SET time_zone = 'Europe/London';
INSERT INTO foo ( bar ) VALUES ( '2008-03-30 00:30:00' ); -- This works
INSERT INTO foo ( bar ) VALUES ( TIMESTAMPADD( HOUR, 1, '2008-03-30 00:30:00' ) ); -- This fails with error 1292, incorrect date time value

In the UK/London time zone, one hour after 2008-03-30 00:30:00 is 2008-03-30 02:30:00 because of the switch to daylight savings time (called BST, locally).

How to repeat:
SET time_zone = 'Europe/London';
SELECT TIMESTAMPADD( HOUR, 1, '2008-03-30 00:30:00' ) AS test;

'2008-03-30 01:30:00'

The correct TIMESTAMP is '2008-03-30 02:30:00', which is one hour after '2008-03-30 00:30:00'.

This value can then not be used in a TIMESTAMP column, as it is an invalid time;

CREATE TABLE  `test`.`foo` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `bar` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
SET time_zone = 'Europe/London';
INSERT INTO foo ( bar ) VALUES( TIMESTAMPADD( HOUR, 1, '2008-03-30 00:30:00' ) );
Incorrect datetime value: '2008-03-30 01:30:00' for column 'bar' at row 1

Suggested fix:
TIMESTAMPADD should convert the local time passed in to UTC, add the appropriate values, then convert back to local time.
[25 Oct 2008 18:57] Greg Thomas
Update the version number; Double checked the same behaviour exists on the latest available version - it still does.
[25 Oct 2008 19:01] Greg Thomas
I've just checked, and TIMESTAMPDIFF suffers from a similar problem. There's only one hour between '2008-03-30 00:30:00' and '2008-03-30 02:30:00' in UK/London, yet

SET time_zone = 'UK/London';
SELECT TIMESTAMPDIFF( HOUR, '2008-03-30 00:30:00', '2008-03-30 02:30:00' );

gives an answer of two. The same solution; i.e. convert the input values from local time to UTC before doing the arithmetic, will resolve this issue, too.
[26 Oct 2008 6:44] Sveta Smirnova
Thank you for the report.

Verified as described.
[24 Nov 2008 13:22] Tatiana Azundris Nuernberg
Let's do the timewarp again!

Just doing a no-frills local->UTC, calc, UTC->local will break badly in pathological cases; consider


to_UTC:    2001-03-01 00:00 -> 2001-02-28,21:00
calc:      2001-02-28,21:00 +1 MONTH => 2001-03-28,21:00
from_UTC:  2001-03-29,00:00
should be: 2001-04-01,00:00
[24 Nov 2008 14:16] Bugs System
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:


2722 Tatiana A. Nurnberg	2008-11-24
      Bug#40329: TIMESTAMPADD does not take in to account Daylight Savings Time
      TIMESTAMPADD()/TIMESTAMPDIFF() took (local-) times at face value.
      This broken on days where DST began or ended as it ignored the
      missing/extra hour.
      We now do our calculations on normalized (UTC) dates when hours
[28 Nov 2008 10:18] Georgi Kodinov
Our documentation says (http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html) : 
"The current session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval. The current time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIME columns."

The above imho implies that dates/times in DATETIME columns are stored/used in UTC. The timezone applies only when converting zone sensitive (e.g. TIMESTAMP) data into DATE/TIME/DATETIME.
If you want locale dependent arithmetic you e.g. can convert to unix time, do the arithmetic and then convert back.
This should probably be stressed in the docs. Moving this bug as a docs bug.
[1 Dec 2008 16:34] Paul DuBois
Changing status to Verified, assigning to myself.
[1 Dec 2008 16:43] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.