(mysql.info.gz) Year 2000 compliance
Info Catalog
(mysql.info.gz) Table size
(mysql.info.gz) What-is
1.2.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 3.22 and later, the `YEAR' column type can store 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.01 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',19981231235959),
-> ('1999-01-01','1999-01-01 00:00:00',19990101000000),
-> ('1999-09-09','1999-09-09 23:59:59',19990909235959),
-> ('2000-01-01','2000-01-01 00:00:00',20000101000000),
-> ('2000-02-28','2000-02-28 00:00:00',20000228000000),
-> ('2000-02-29','2000-02-29 00:00:00',20000229000000),
-> ('2000-03-01','2000-03-01 00:00:00',20000301000000),
-> ('2000-12-31','2000-12-31 23:59:59',20001231235959),
-> ('2001-01-01','2001-01-01 00:00:00',20010101000000),
-> ('2004-12-31','2004-12-31 23:59:59',20041231235959),
-> ('2005-01-01','2005-01-01 00:00:00',20050101000000),
-> ('2030-01-01','2030-01-01 00:00:00',20300101000000),
-> ('2040-01-01','2040-01-01 00:00:00',20400101000000),
-> ('9999-12-31','9999-12-31 23:59:59',99991231235959);
Query OK, 14 rows affected (0.01 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 | 19981231235959 |
| 1999-01-01 | 1999-01-01 00:00:00 | 19990101000000 |
| 1999-09-09 | 1999-09-09 23:59:59 | 19990909235959 |
| 2000-01-01 | 2000-01-01 00:00:00 | 20000101000000 |
| 2000-02-28 | 2000-02-28 00:00:00 | 20000228000000 |
| 2000-02-29 | 2000-02-29 00:00:00 | 20000229000000 |
| 2000-03-01 | 2000-03-01 00:00:00 | 20000301000000 |
| 2000-12-31 | 2000-12-31 23:59:59 | 20001231235959 |
| 2001-01-01 | 2001-01-01 00:00:00 | 20010101000000 |
| 2004-12-31 | 2004-12-31 23:59:59 | 20041231235959 |
| 2005-01-01 | 2005-01-01 00:00:00 | 20050101000000 |
| 2030-01-01 | 2030-01-01 00:00:00 | 20300101000000 |
| 2040-01-01 | 2040-01-01 00:00:00 | 00000000000000 |
| 9999-12-31 | 9999-12-31 23:59:59 | 00000000000000 |
+------------+---------------------+----------------+
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 `19700101000000' to `20300101000000' 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
Y2K issues:: for MySQL Server's rules for dealing with ambiguous date
input data that contains two-digit year values.
Info Catalog
(mysql.info.gz) Table size
(mysql.info.gz) What-is
automatically generated byinfo2html