1.4.5. Year 2000 Compliance
The MySQL Server itself has no problems with Year 2000 (Y2K)
compliance:
MySQL Server uses Unix time functions that handle dates into
the year 2037 for
TIMESTAMP values. For
DATE and DATETIME
values, dates through the year 9999 are
accepted.
All MySQL date functions are implemented in one source file,
sql/time.cc, and are coded very carefully
to be year 2000-safe.
In MySQL, the YEAR data type can store the
years 0 and 1901 to
2155 in one byte and display them using two
or four digits. All two-digit years are considered to be in
the range 1970 to 2069,
which means that if you store 01 in a
YEAR column, MySQL Server treats it as
2001.
The following simple demonstration illustrates that MySQL Server
has no problems with DATE or
DATETIME values through the year 9999, and no
problems with TIMESTAMP values until after the
year 2030:
mysql> DROP TABLE IF EXISTS y2k;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE y2k (date DATE,
-> date_time DATETIME,
-> time_stamp TIMESTAMP);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO y2k VALUES
-> ('1998-12-31','1998-12-31 23:59:59','1998-12-31 23:59:59'),
-> ('1999-01-01','1999-01-01 00:00:00','1999-01-01 00:00:00'),
-> ('1999-09-09','1999-09-09 23:59:59','1999-09-09 23:59:59'),
-> ('2000-01-01','2000-01-01 00:00:00','2000-01-01 00:00:00'),
-> ('2000-02-28','2000-02-28 00:00:00','2000-02-28 00:00:00'),
-> ('2000-02-29','2000-02-29 00:00:00','2000-02-29 00:00:00'),
-> ('2000-03-01','2000-03-01 00:00:00','2000-03-01 00:00:00'),
-> ('2000-12-31','2000-12-31 23:59:59','2000-12-31 23:59:59'),
-> ('2001-01-01','2001-01-01 00:00:00','2001-01-01 00:00:00'),
-> ('2004-12-31','2004-12-31 23:59:59','2004-12-31 23:59:59'),
-> ('2005-01-01','2005-01-01 00:00:00','2005-01-01 00:00:00'),
-> ('2030-01-01','2030-01-01 00:00:00','2030-01-01 00:00:00'),
-> ('2040-01-01','2040-01-01 00:00:00','2040-01-01 00:00:00'),
-> ('9999-12-31','9999-12-31 23:59:59','9999-12-31 23:59:59');
Query OK, 14 rows affected, 2 warnings (0.00 sec)
Records: 14 Duplicates: 0 Warnings: 2
mysql> SELECT * FROM y2k;
+------------+---------------------+---------------------+
| date | date_time | time_stamp |
+------------+---------------------+---------------------+
| 1998-12-31 | 1998-12-31 23:59:59 | 1998-12-31 23:59:59 |
| 1999-01-01 | 1999-01-01 00:00:00 | 1999-01-01 00:00:00 |
| 1999-09-09 | 1999-09-09 23:59:59 | 1999-09-09 23:59:59 |
| 2000-01-01 | 2000-01-01 00:00:00 | 2000-01-01 00:00:00 |
| 2000-02-28 | 2000-02-28 00:00:00 | 2000-02-28 00:00:00 |
| 2000-02-29 | 2000-02-29 00:00:00 | 2000-02-29 00:00:00 |
| 2000-03-01 | 2000-03-01 00:00:00 | 2000-03-01 00:00:00 |
| 2000-12-31 | 2000-12-31 23:59:59 | 2000-12-31 23:59:59 |
| 2001-01-01 | 2001-01-01 00:00:00 | 2001-01-01 00:00:00 |
| 2004-12-31 | 2004-12-31 23:59:59 | 2004-12-31 23:59:59 |
| 2005-01-01 | 2005-01-01 00:00:00 | 2005-01-01 00:00:00 |
| 2030-01-01 | 2030-01-01 00:00:00 | 2030-01-01 00:00:00 |
| 2040-01-01 | 2040-01-01 00:00:00 | 0000-00-00 00:00:00 |
| 9999-12-31 | 9999-12-31 23:59:59 | 0000-00-00 00:00:00 |
+------------+---------------------+---------------------+
14 rows in set (0.00 sec)
The final two TIMESTAMP column values are zero
because the year values (2040,
9999) exceed the TIMESTAMP
maximum. The TIMESTAMP data type, which is used
to store the current time, supports values that range from
'1970-01-01 00:00:00' to '2030-01-01
00:00:00' on 32-bit machines (signed value). On 64-bit
machines, TIMESTAMP handles values up to
2106 (unsigned value).
Although MySQL Server itself is Y2K-safe, you may run into
problems if you use it with applications that are not Y2K-safe.
For example, many old applications store or manipulate years using
two-digit values (which are ambiguous) rather than four-digit
values. This problem may be compounded by applications that use
values such as 00 or 99 as
“missing” value indicators. Unfortunately, these
problems may be difficult to fix because different applications
may be written by different programmers, each of whom may use a
different set of conventions and date-handling functions.
Thus, even though MySQL Server has no Y2K problems, it
is the application's responsibility to provide unambiguous
input. See Section 11.3.4, “Y2K Issues and Date Types”, for MySQL
Server's rules for dealing with ambiguous date input data that
contains two-digit year values.
