DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(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