MySQL 5.6 Datetime Range

Question

According to MySQL 5.6 doc, the supported range for datetime data type is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. But when I tried inserting values that are earlier than '1000-01-01 00:00:00', I received no error and the values were successfully stored in the table.

CREATE TABLE `test` (
  `a` datetime(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

mysql> insert into test values ('999-01-01 01:01:01');
Query OK, 1 row affected (1.18 sec)

mysql> select * from test;
+-------------------------+
| a                       |
+-------------------------+
| 0999-01-01 01:01:01.000 |
+-------------------------+

mysql> insert into test values ('1-01-01 01:01:01');
Query OK, 1 row affected (0.08 sec)

mysql> select * from test;
+-------------------------+
| a                       |
+-------------------------+
| 0999-01-01 01:01:01.000 |
| 0001-01-01 01:01:01.000 |
+-------------------------+

Does this mean that the documentation is wrong? I am using mysql version 5.6.21.

Answer 1

OBSERVATIONS

According to the MySQL 5.0 Documentation

As of 5.0.2, the server requires that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error. To permit such dates, enable ALLOW_INVALID_DATES. See Section 5.1.7, “Server SQL Modes”, for more information.

Before MySQL 5.0.2, the MySQL server performs only basic checking on the validity of a date: The ranges for year, month, and day are 1000 to 9999, 00 to 12, and 00 to 31, respectively. Any date containing parts not within these ranges is subject to conversion to '0000-00-00'. Please note that this still permits you to store invalid dates such as '2002-04-31'. To ensure that a date is valid, you should perform a check in your application.

The second paragraph is not in the 5.1, 5.5, or 5.6 Documentation. This may have been left out of the Documentation when MySQL 5.1 went GA. If this is still happening, this is a big oversight.

Let try this out on MySQL 5.5.37

mysql> create database bluecollarcoder;
Query OK, 1 row affected (0.00 sec)

mysql> use bluecollarcoder;
Database changed
mysql> CREATE TABLE `test` (
    ->   `a` datetime NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values ('999-01-01 01:01:01');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values ('1-01-01 01:01:01');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+---------------------+
| a                   |
+---------------------+
| 0999-01-01 01:01:01 |
| 0001-01-01 01:01:01 |
+---------------------+
2 rows in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.37-log |
+------------+
1 row in set (0.00 sec)

mysql>

I just did this on my laptop running MySQL 5.5.37 for Windows. Same Problem.

You will have to check for invalid dates in your code.

SUGGESTION

If you want to fix all dates so that invalid datetimes are defaulted to a valid datetime, you can run this:

update test set a = from_unixtime(unix_timestamp(a));

When I ran it, it set to FROM_UNIXTIME(0) in my timezone

mysql> update test set a = from_unixtime(unix_timestamp(a));
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select a,unix_timestamp(a) from test;
+---------------------+-------------------+
| a                   | unix_timestamp(a) |
+---------------------+-------------------+
| 1969-12-31 19:00:00 |                 0 |
| 1969-12-31 19:00:00 |                 0 |
+---------------------+-------------------+
2 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!