DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(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