(mysql.info) year-2000-compliance
Info Catalog
(mysql.info) table-size
(mysql.info) what-is
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
y2k-issues, for MySQL Server's rules for dealing with ambiguous date
input data that contains two-digit year values.
Info Catalog
(mysql.info) table-size
(mysql.info) what-is
automatically generated byinfo2html