MySQL 5.6 Datetime Range

datatypesdatetimeMySQLmysql-5.6

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.

Best Answer

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 !!!

PROBLEM

You need to insert nothing ?

SOLUTION

Then, insert nothing at all ... literally !!!

One of the following will work

INSERT INTO test2 () VALUES (),(),();
INSERT INTO test2 (test) VALUES (NULL),(NULL),(NULL);
INSERT INTO test2 (id) VALUES (NULL),(NULL),(NULL);

SAMPLE DATA

use test
DROP TABLE IF EXISTS test2;
CREATE TABLE test2
(id int not null auto_increment primary key,
test int not null default 0);
SHOW CREATE TABLE test2\G
SELECT * FROM test2;

SAMPLE DATA LOADED

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS test2;
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE TABLE test2
    -> (id int not null auto_increment primary key,
    -> test int default 0);
Query OK, 0 rows affected (0.34 sec)

mysql> SHOW CREATE TABLE test2\G
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT * FROM test2;
Empty set (0.00 sec)

mysql>

QUERIES EXECUTED

If id and test are the only two columns in the table, the INSERT does not need a column list. If there are other columns in test2 besides id and test, name the test column and insert a NULL. Keep in mind, inserting a NULL into a column that has DEFAULT 0 will become 0.

mysql> SELECT * FROM test2;
Empty set (0.00 sec)

mysql> INSERT INTO test2 () VALUES (),(),();
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test2 (test) VALUES (NULL),(NULL),(NULL);
Query OK, 3 rows affected, 3 warnings (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 3

mysql> INSERT INTO test2 (id) VALUES (NULL),(NULL),(NULL);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test2;
+----+------+
| id | test |
+----+------+
|  1 |    0 |
|  2 |    0 |
|  3 |    0 |
|  4 |    0 |
|  5 |    0 |
|  6 |    0 |
|  7 |    0 |
|  8 |    0 |
|  9 |    0 |
+----+------+
9 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

CAVEAT #1 : I did this in Windows 8.1 from the command line

mysql> select * from information_schema.global_variables
    -> where variable_name like 'version%';
+-------------------------+------------------------------+
| VARIABLE_NAME           | VARIABLE_VALUE               |
+-------------------------+------------------------------+
| VERSION_COMMENT         | MySQL Community Server (GPL) |
| VERSION_COMPILE_MACHINE | x86_64                       |
| VERSION_COMPILE_OS      | Win64                        |
| VERSION                 | 5.6.15                       |
+-------------------------+------------------------------+
4 rows in set (0.07 sec)

mysql>

CAVEAT #2 : I am using MySQL's default SQL mode. Thus, I did not need to disable anything.

Best Answer

OBSERVATIONS

SUGGESTION

GIVE IT A TRY !!!

Related Solutions

Mysql – find and insert row to another table using thesql trigger

Mysql – Inserting empty string on Windows MySQL doesn’t use default value

PROBLEM

SOLUTION

SAMPLE DATA

SAMPLE DATA LOADED

QUERIES EXECUTED

GIVE IT A TRY !!!

Related Question