MySQL Bugs: #15190: TIMESTAMP data type is not parsed correctly

Bug #15190	TIMESTAMP data type is not parsed correctly
Submitted:	23 Nov 2005 16:03	Modified:	22 Dec 2005 20:15
Reporter:	Linus Larsson	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.16	OS:	Windows (Windows XP)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
There are two issues, one which might be a non-issue (I think it is, but others might not).

The first one is, while running in sql_mode='traditional' (with NO_ZERO_DATE), a timestamp returned by from_unixtime(0) is considered invalid and yields an "ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 01:00:00'". I don't think the very beginning of the epoch should be considered out of range.

The other one, which is more serious, is that the timestamp '1970-01-01 00:00:01' is accepted, but is being stored as '1970-01-02 00:00:02'. This is 100% repeatable in both InnoDB and MyISAM tables, and regardless of sql_mode.

How to repeat:
DROP DATABASE test;
CREATE DATABASE test;
USE test;
CREATE TABLE timestamp_test (id BIGINT NOT NULL AUTO_INCREMENT, test_timestamp timestamp NULL, PRIMARY KEY(id)) Type=MyISAM;

BEGIN;
INSERT INTO timestamp_test (test_timestamp) VALUES ('1970-01-01 01:00:00');

INSERT INTO timestamp_test (test_timestamp) VALUES ('1970-01-01 00:00:01');

INSERT INTO timestamp_test (test_timestamp) VALUES ('1970-01-01 00:00:02');

INSERT INTO timestamp_test (test_timestamp) VALUES ('1970-01-01 00:01:00');

INSERT INTO timestamp_test (test_timestamp) VALUES ('1970-01-01 00:02:00');

COMMIT;

SELECT * FROM timestamp_test;

And the output is:

+----+---------------------+
| id | test_timestamp      |
+----+---------------------+
|  1 | 1970-01-02 00:00:02 |
|  2 | 1970-01-02 00:00:04 |
|  3 | 1970-01-02 00:02:00 |
|  4 | 1970-01-02 00:04:00 |
+----+---------------------+
4 rows in set (0.02 sec)

I was unable to reproduce this bug under both, FC4 and WinXP:

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT * FROM timestamp_test;
+----+---------------------+
| id | test_timestamp      |
+----+---------------------+
|  1 | 1970-01-01 01:00:00 |
|  2 | 1970-01-01 00:00:01 |
|  3 | 1970-01-01 00:00:02 |
|  4 | 1970-01-01 00:01:00 |
|  5 | 1970-01-01 00:02:00 |
+----+---------------------+
5 rows in set (0.02 sec)

mysql>

Well, the timestamp in the first row ('1970-01-01 01:00:00') is, I guess, disallowed because I'm in the timezone UTC+1. So if you're in any other timezone, try to issue

INSERT INTO timestamp_test (test_timestamp) VALUES (from_unixtime(0));

instead, and see if you get an error. It's enlighting that you don't get the other timestamps wrong - could it be timezone related?

Verified; if I set my timezone to GMT, it works as expected. I came across this which might be interesting, though SELECT NOW(); returns the correct time on my installation: http://dev.mysql.com/doc/refman/5.0/en/timezone-problems.html

I was unable to repeat with current released server. I got the
same result as Jorge.