GNU Info

(mysql.info)Year 2000 compliance

---

Prev: Table size Up: MySQL and MySQL AB

---

Enter node , (file) or (file)node

Year 2000 Compliance
--------------------

MySQL itself has no problems with Year 2000 (Y2K) compliance:

   * MySQL uses Unix time functions and has no problems with dates
     until `2069'; all 2-digit years are regarded to be in the range
     `1970' to `2069', which means that if you store `01' in a `year'
     column, MySQL treats it as `2001'.

   * All MySQL date functions are stored in one file `sql/time.cc' and
     coded very carefully to be year 2000-safe.

   * In MySQL Version 3.22 and later, the new `YEAR' column type can
     store years `0' and `1901' to `2155' in 1 byte and display them
     using 2 or 4 digits.

You may run into problems with applications that use MySQL in a way
that is not Y2K-safe.  For example, many old applications store or
manipulate years using 2-digit values (which are ambiguous) rather than
4-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.

Here is a simple demonstration illustrating that MySQL doesn't have any
problems with dates until 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.00 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),
         -> ("2050-01-01","2050-01-01 00:00:00",20500101000000);
     Query OK, 13 rows affected (0.01 sec)
     Records: 13  Duplicates: 0  Warnings: 0
     
     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 |
     | 2050-01-01 | 2050-01-01 00:00:00 | 00000000000000 |
     +------------+---------------------+----------------+
     13 rows in set (0.00 sec)

This shows that the `DATE' and `DATETIME' types will not give any
problems with future dates (they handle dates until the year 9999).

The `TIMESTAMP' type, which is used to store the current time, has a
range up to only `2030-01-01'.  `TIMESTAMP' has a range of `1970' to
`2030' on 32-bit machines (signed value).  On 64-bit machines it
handles times up to `2106' (unsigned value).

Even though MySQL is Y2K-compliant, it is your responsibility to
provide unambiguous input.  See Note: Y2K issues for MySQL's rules
for dealing with ambiguous date input data (data containing 2-digit year
values).