Description:
When inserting data into the server that approaches the lower limit of valid values for a timestamp field, the date portion is incorrectly converted when selecting the same data. 2069 is an invalid date for a timestamp field. See below a test on 4.1.16:
---------
mysql> set time_zone='-6:00';
Query OK, 0 rows affected (0.06 sec)
mysql> drop table if exists time_test;
Query OK, 0 rows affected (0.05 sec)
mysql> create table time_test (id integer auto_increment, stamp timestamp,
-> primary key (id));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into time_test values (0,0);
Query OK, 1 row affected (0.00 sec)
mysql> insert into time_test values (0,19691231190001);
Query OK, 1 row affected (0.00 sec)
mysql> insert into time_test values (0,19691231180001);
Query OK, 1 row affected (0.00 sec)
mysql> insert into time_test values (0,19691231170001);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into time_test values (0,19691231160001);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from time_test;
+----+---------------------+
| id | stamp |
+----+---------------------+
| 1 | 0000-00-00 00:00:00 |
| 2 | 2069-12-31 19:00:01 | <----------INVALID!!
| 3 | 2069-12-31 18:00:01 | <----------INVALID!!
| 4 | 0000-00-00 00:00:00 |
| 5 | 0000-00-00 00:00:00 |
+----+---------------------+
5 rows in set (0.02 sec)
mysql> select id, date(stamp) from time_test;
+----+-------------+
| id | date(stamp) |
+----+-------------+
| 1 | 0000-00-00 |
| 2 | 1969-12-31 |
| 3 | 1969-12-31 |
| 4 | 0000-00-00 |
| 5 | 0000-00-00 |
+----+-------------+
5 rows in set (0.01 sec)
mysql> set time_zone='+0:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from time_test;
+----+---------------------+
| id | stamp |
+----+---------------------+
| 1 | 0000-00-00 00:00:00 |
| 2 | 1970-01-01 01:00:01 |
| 3 | 1970-01-01 00:00:01 |
| 4 | 0000-00-00 00:00:00 |
| 5 | 0000-00-00 00:00:00 |
+----+---------------------+
5 rows in set (0.00 sec)
mysql>
--------
How to repeat:
set time_zone='-6:00';
drop table if exists time_test;
create table time_test (id integer auto_increment, stamp timestamp, primary key (id));
insert into time_test values (0,0);
insert into time_test values (0,19691231190001);
insert into time_test values (0,19691231180001);
insert into time_test values (0,19691231170001);
insert into time_test values (0,19691231160001);
select * from time_test;
select id, date(stamp) from time_test;
set time_zone='+0:00';
select * from time_test;
Suggested fix:
Not sure. It looks like some bug, just because "2069-12-31 18:00:01" is completely invalid. Maybe the conversion back to UTC from a value less than UTC (-x:yy) is not working correctly, because if you set time_zone='+x:yy'; then it's all correct.
Description: When inserting data into the server that approaches the lower limit of valid values for a timestamp field, the date portion is incorrectly converted when selecting the same data. 2069 is an invalid date for a timestamp field. See below a test on 4.1.16: --------- mysql> set time_zone='-6:00'; Query OK, 0 rows affected (0.06 sec) mysql> drop table if exists time_test; Query OK, 0 rows affected (0.05 sec) mysql> create table time_test (id integer auto_increment, stamp timestamp, -> primary key (id)); Query OK, 0 rows affected (0.05 sec) mysql> insert into time_test values (0,0); Query OK, 1 row affected (0.00 sec) mysql> insert into time_test values (0,19691231190001); Query OK, 1 row affected (0.00 sec) mysql> insert into time_test values (0,19691231180001); Query OK, 1 row affected (0.00 sec) mysql> insert into time_test values (0,19691231170001); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into time_test values (0,19691231160001); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from time_test; +----+---------------------+ | id | stamp | +----+---------------------+ | 1 | 0000-00-00 00:00:00 | | 2 | 2069-12-31 19:00:01 | <----------INVALID!! | 3 | 2069-12-31 18:00:01 | <----------INVALID!! | 4 | 0000-00-00 00:00:00 | | 5 | 0000-00-00 00:00:00 | +----+---------------------+ 5 rows in set (0.02 sec) mysql> select id, date(stamp) from time_test; +----+-------------+ | id | date(stamp) | +----+-------------+ | 1 | 0000-00-00 | | 2 | 1969-12-31 | | 3 | 1969-12-31 | | 4 | 0000-00-00 | | 5 | 0000-00-00 | +----+-------------+ 5 rows in set (0.01 sec) mysql> set time_zone='+0:00'; Query OK, 0 rows affected (0.00 sec) mysql> select * from time_test; +----+---------------------+ | id | stamp | +----+---------------------+ | 1 | 0000-00-00 00:00:00 | | 2 | 1970-01-01 01:00:01 | | 3 | 1970-01-01 00:00:01 | | 4 | 0000-00-00 00:00:00 | | 5 | 0000-00-00 00:00:00 | +----+---------------------+ 5 rows in set (0.00 sec) mysql> -------- How to repeat: set time_zone='-6:00'; drop table if exists time_test; create table time_test (id integer auto_increment, stamp timestamp, primary key (id)); insert into time_test values (0,0); insert into time_test values (0,19691231190001); insert into time_test values (0,19691231180001); insert into time_test values (0,19691231170001); insert into time_test values (0,19691231160001); select * from time_test; select id, date(stamp) from time_test; set time_zone='+0:00'; select * from time_test; Suggested fix: Not sure. It looks like some bug, just because "2069-12-31 18:00:01" is completely invalid. Maybe the conversion back to UTC from a value less than UTC (-x:yy) is not working correctly, because if you set time_zone='+x:yy'; then it's all correct.