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
Triage: Triaged: D2 (Serious)

[30 Jul 2008 12:38] Domas Mituzas
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.
[3 Aug 2009 14:38] Martin Stjernholm
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 | 
+--------------------+