Bug #16327 invalid TIMESTAMP values retrieved
Submitted: 10 Jan 2006 8:29 Modified: 24 Jul 2006 16:47
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.16, 5.0.18, 5.0.19 OS:Linux (linux, windows)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[10 Jan 2006 8:29] Shane Bester
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.
[30 Mar 2006 11:24] MySQL Verification Team
any progress on this?
[12 Jul 2006 18:35] Iggy Galarza
Patch is available here: http://lists.mysql.com/commits/6506
[13 Jul 2006 1:11] Timothy Smith
Ramil,

OK to push.  Thanks!
[23 Jul 2006 11:52] Ramil Kalimullin
fixed in 4.1.22
[24 Jul 2006 16:47] Paul DuBois
Noted in 4.1.22 changelog.

Conversion of TIMESTAMP values between UTF and the local time zone
resulted in some values having the year 2069 rather than 1969.
[24 Jul 2006 17:58] Paul DuBois
Mistake in previous comment: Conversion is between *UTC* and the local time zone.