MySQL Bugs: #40329: TIMESTAMPADD does not take in to account Daylight Savings Time.

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:	None
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

Description:
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;

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`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
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.

Update the version number; Double checked the same behaviour exists on the latest available version - it still does.

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.

Thank you for the report.

Verified as described.

Let's do the timewarp again!

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

TZ=+3
SELECT TIMESTAMPADD(MONTH,1,'2001-03-01')

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

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/59681

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
      matter.

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.

Changing status to Verified, assigning to myself.

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.