MySQL Bugs: #22276: Can not insert MIN TIMESTAMP value

Bug #22276	Can not insert MIN TIMESTAMP value
Submitted:	12 Sep 2006 16:28	Modified:	6 Nov 2006 14:38
Reporter:	Tonci Grgin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S1 (Critical)
Version:	4.1, 5.0, 5.1BK	OS:	Windows (Win XP, Suse 10.0)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
I'm unable to insert minimal TIMESTAMP value into MySQL server. All servers are BK build with empty TZ tables. My TZ is GMT+1.

Suse:
mysql-5.1 # bk changes|head
ChangeSet@1.2317, 2006-09-09 10:39:12+02:00, serg@janus.mylan +1 -0
  bugfix
ChangeSet@1.2315, 2006-09-07 16:17:16-07:00, brian@zim.(none) +3 -0
  Warning fixes for Windows, and an include fix for Windows for Innodb.

mysql-5.0 # bk changes|head
ChangeSet@1.2263, 2006-09-08 16:16:39+05:00, gluh@mysql.com +4 -0
  after merge fix
ChangeSet@1.2260.1.2, 2006-09-07 17:51:28+02:00, joerg@trift2. +1 -0
  configure.in  :    NDB version and general version must be (kept) in sync - do that.

mysql-4.1 # bk changes|head
ChangeSet@1.2563, 2006-09-04 16:53:03+03:00, timour@lamia.home +1 -0
  BUG#21787: COUNT(*) + ORDER BY + LIMIT returns wrong result
  Fix an error in the bug fix.
ChangeSet@1.2546.1.4, 2006-09-04 14:45:13+02:00, jonas@perch.ndb.mysql.com +1 -0
  Merge perch.ndb.mysql.com:/home/jonas/src/41-work
  into  perch.ndb.mysql.com:/home/jonas/src/mysql-4.1-ndb
  MERGE: 1.2527.4.13

Win XP: 5.0.24-log BK

How to repeat:
drop table if exists test;
create table test (
  lastHourlyCalculationTime TIMESTAMP NOT NULL DEFAULT current_timestamp,
  lastDailyCalculationTime TIMESTAMP NOT NULL, 
  creationTime TIMESTAMP NOT NULL);
INSERT INTO test (lastHourlyCalculationTime,lastDailyCalculationTime, creationTime) VALUES('1970-01-01 01:00:00','1970-01-01 01:00:00','1970-01-01 01:00:00');
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------+
| Level   | Code | Message                                                            |
+---------+------+--------------------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'lastHourlyCalculationTime' at row 1 |
| Warning | 1264 | Out of range value for column 'lastDailyCalculationTime' at row 1  |
| Warning | 1264 | Out of range value for column 'creationTime' at row 1              |
+---------+------+--------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM test;
+---------------------------+--------------------------+---------------------+
| lastHourlyCalculationTime | lastDailyCalculationTime | creationTime        |
+---------------------------+--------------------------+---------------------+
|       0000-00-00 00:00:00 |      0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+---------------------------+--------------------------+---------------------+
1 row in set (0.00 sec)

mysql>

INSERT INTO test (lastHourlyCalculationTime,lastDailyCalculationTime, creationTime) VALUES('1970-01-01 01:00:01','1970-01-01 01:00:01','1970-01-01 01:00:01');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+---------------------------+--------------------------+---------------------+
| lastHourlyCalculationTime | lastDailyCalculationTime | creationTime        |
+---------------------------+--------------------------+---------------------+
|       0000-00-00 00:00:00 |      0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|       1970-01-01 01:00:01 |      1970-01-01 01:00:01 | 1970-01-01 01:00:01 |
+---------------------------+--------------------------+---------------------+
2 rows in set (0.00 sec)

mysql>

I, too, have been bitten by this bug.

Furthermore, when you do a query with beginning of epoch from Java (jdbc Timestamp(0)) the value in the query is '1970-01-01 01:00:00'; ergo, no matches are found.

Hi, after further investigation I must say it was never possible to insert date equivalent to the start of Unix Epoch (aka 1970-01-01 00:00:00 UTC) into TIMESTAMP field as corresponding value in internal representation of TIMESTAMP is occupied by special 0 datetime. I'm changing category as this needs better documenting.

I have upated the manual with the correct lower bound on
the range.

The time part of the lower bound has been changed in the manual
from 00:00:00 to 00:00:01, for example here:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html

Which also explains why 1970-01-01 00:00:00 cannot be stored in a
TIMESTAMP column.