MySQL time types and modes details

When I try to insert a timestamp into a MySQL database, I get an error:

mysql> insert into alarm_service values ​​(6, '1970-01-01 08:00:00'); 
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 08:00:00' for column 'time' at row 1

# View the table structure mysql> show create table alarm_service;
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| alarm_service | CREATE TABLE `alarm_service` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

We can see that the error message indicates that the time value is wrong, but this is obviously a valid time point.

After searching the data, I found that the reason is that in MySQL , the legal interval of timestamp type is 1970-01-01 00:00:01 - 2038-01-19 03:14:07 UTC， When storing, the data you insert will be converted to UTC time first, then stored, and converted to your local time when reading. Since my time zone is East 8, the conversion becomes 1970-01-01 00:00:00 UTC , which is an illegal time.

The solution is:

1. Adjust the time to the legal range
2. Adjust MySQL strict mode to allow illegal times
   

Below we explain the relevant content in detail.

1. MySQL time type

MySQL time types are divided into three types :

• DATE: When it contains only the date but not the time, MySQL will convert the format to YYYY-MM-DD , and the legal range is 1000-01-01 - 9999-12-31 .
• DATETIME: When used to contain date + time, the format is YYYY-MM-DD HH:MM:SS , and the legal range is 1000-01-01 00:00:00 - 9999-12-31 23:59:59 .
• TIMESTAMP: When used to contain date + time, the format is YYYY-MM-DD HH:MM:SS , and the legal range is 1997-01-01 00:00:01 - 2038-01-19 03:14:07 UTC .
  

At the same time, DATETIME and TIMESTAMP also support a 6-digit microsecond data support, the format is YYYY-MM-DD HH:MM:SS[.fraction] , and the legal range is .000000 - .999999 .

Both DATETIME and TIMESTAMP also provide data that is automatically initialized and updated to the current date and time.

For the TIMESTAMP type, MySQL converts the data value to UTC standard time when storing it, and converts it to the current time when reading it. If your time zone does not change, the value is what you stored. If you change the time zone, the value you read will change. This feature does not work for DATETIME .

2. Check the time zone

mysql> show variables like '%zone%';                                       
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+

You can see that the current time zone is SYSTEM, which is consistent with the operating system. The system time zone is CST (China Standard Time). You can also see that the system time is East 8 (+0800):

$ date -R
Tue, 23 Apr 2019 11:22:47 +0800

Therefore, when we enter 1970-01-01 08:00:00 , MySQL will correct it to 1970-01-01 00:00:00， which becomes an illegal value.

3. Illegal time value

For illegal time values, MySQL converts them to appropriate values ​​for different time types: 0000-00-00 or 0000-00-00 00:00:00 .

For example, if the months are January to December, when you try to insert 2019-13-01 00:00:00 , it will be corrected to 0000-00-00 00:00:00 , because there is no 13th month, which is an illegal value.

4. Strict mode

When we insert an illegal time value, it will be corrected, but in strict mode, the data will not be inserted and an error will be reported instead:

ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 08:00:00' for column 'time' at row 1

We can adjust the behavior of MySQL by setting the mode. First, check the MySQL mode:

mysql> show variables like '%sql_mode%';            
+----------------------------+--------------------------------------------+
| Variable_name | Value |
+----------------------------+--------------------------------------------+ |
| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+----------------------------+--------------------------------------------+

In this mode, illegal dates will be reported as errors. We can adjust the mode to ALLOW_INVALID_DATES :

mysql> set session sql_mode = 'ALLOW_INVALID_DATES';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%sql_mode%';            
+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| sql_mode | ALLOW_INVALID_DATES |
+---------------+---------------------+
1 row in set (0.00 sec)

In this mode, the date is no longer fully checked for validity, only the month is checked to be in the range 1-12 and the day is in the range 1-31. This is suitable when processing user input, but this mode is only suitable for DATE and DATETIME . For TIMESTAMP , a valid value is still required, otherwise it will be corrected to 0000-00-00 00:00:00.

In the case of illegal values, an error will be reported if this mode is enabled; if disabled, it will be corrected to 0000-00-00 00:00:00 and a warning will be generated:

mysql> insert into alarm_service values ​​(7, '1970-01-01 08:00:00'); 
Query OK, 1 row affected, 1 warning (0.00 sec)

Summarize:

There are two solutions to this problem:

1. Adjust the time to the legal range
2. Adjust MySQL strict mode to allow illegal times

5. Case summary

ERROR 1067 (42000): Invalid default value for 'createTime'


Check the reason and find that it is set to:

# View the statement to create the table CREATE TABLE `dimensionsConf` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `createTime` datetime DEFAULT CURRENT_TIMESTAMP,
) ENGINE=InnoDB AUTO_INCREMENT=178 DEFAULT CHARSET=utf8;

# View the database version $mysql --version
mysql Ver 14.14 Distrib 5.1.30, for unknown-linux-gnu (x86_64) using EditLine wrapper

This is the end of this article about MySQL time types and modes. For more information about MySQL time types and modes, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!