MySQL Bugs: #38457: NOW() loses time information when writing to TIMESTAMP field

Bug #38457	NOW() loses time information when writing to TIMESTAMP field
Submitted:	30 Jul 2008 12:38
Reporter:	Domas Mituzas	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	4.1, 5.0, 5.1, etc	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	daylight, timestamp, timezone

Description:
In case NOW() is used after time has been shifted back an hour, it will not have that information written to TIMESTAMP field, therefore providing with truncated/stripped/ambiguous data.

How to repeat:
mysql> create table timestamptest (a timestamp, b timestamp);
Query OK, 0 rows affected (0.07 sec)

mysql> set timestamp=1224972000+10800;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into timestamptest values(NULL,now());
Query OK, 1 row affected (0.02 sec)

mysql> set timestamp=1224972000+7200;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into timestamptest values(NULL,now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from timestamptest;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2008-10-26 03:00:00 | 2008-10-26 03:00:00 | 
| 2008-10-26 03:00:00 | 2008-10-26 03:00:00 | 
+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> set time_zone=UTC;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from timestamptest;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2008-10-26 01:00:00 | 2008-10-26 00:00:00 | 
| 2008-10-26 00:00:00 | 2008-10-26 00:00:00 | 
+---------------------+---------------------+
2 rows in set (0.00 sec)

Suggested fix:
retain proper unix_timestamp information in case of now(), and if it is inserted to TIMESTAMP column, use the real value, not the display value.

This problem also affects FROM_UNIXTIME(). One would expect that having a TIMESTAMP column which is always set through FROM_UNIXTIME() and always read through UNIX_TIMESTAMP() would be safe from calendaric discontinuities etc, but that is not so.

An example, assuming that the active time zone on the connection is Europe/Stockholm (aka CET):

mysql> CREATE TABLE test (ts TIMESTAMP DEFAULT 0);
mysql> INSERT INTO test SET ts = FROM_UNIXTIME(1130630400);
mysql> SELECT UNIX_TIMESTAMP(ts) FROM test;
+--------------------+
| UNIX_TIMESTAMP(ts) |
+--------------------+
|         1130634000 | 
+--------------------+

Note the 1 hour difference between the set value and the retrieved one.

A workaround is to change the timezone on the connection to one that doesn't use DST:

mysql> SET time_zone = '+00:00';
mysql> CREATE TABLE test (ts TIMESTAMP DEFAULT 0);
mysql> INSERT INTO test SET ts = FROM_UNIXTIME(1130630400);
mysql> SELECT UNIX_TIMESTAMP(ts) FROM test;
+--------------------+
| UNIX_TIMESTAMP(ts) |
+--------------------+
|         1130630400 | 
+--------------------+