1

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.

  • I'll hazard that in this case, it being not supported doesn't mean it's not possible but rather that you're using it at your own risk. – LowlyDBA Dec 16 '14 at 19:46
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 !!!

  • My question is more about WHY. If the date '999-01-01 00:00:01' can be stored in the datetime column properly and can be retrieved from the datetime column properly, why is it an invalid date value? – bluecollarcoder Dec 19 '14 at 23:32
  • You originally asked if the documentation is wrong. I just showed you that it is wrong because MySQL left the fact about storing bad dates in the table back in the 5.0 docs. MySQL also said you should check date validity before saving it. As I also showed, any date dt where UNIX_TIMESTAMP(dt) is 0 is obviously invalid. Evidently, the storage engines are not checking the date field and neither is mysqld. You need to ask oracle why by means of a bug report. Setting the SQL mode won't address this either. – RolandoMySQLDBA Dec 20 '14 at 0:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.