MySQL time type and mode

Time:2019-12-7

When I was inMySQLAn error is reported when trying to insert a data with a time stamp in the database:

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 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 find that the error message prompt is a time value error, but this is obviously a legal time point.

After searching the data, we found that the reason isMySQLMedium,timestampThe legal range of the type is1970-01-01 00:00:01 - 2038-01-19 03:14:07 UTC, and in storage, the data you insert will be converted toUTCTime is then stored, read, and converted to your local time. Since my time zone is East 8, it will change to1970-01-01 00:00:00 UTC, becomes illegal time.

The solution is:

  1. Adjust time to legal range
  2. adjustmentMySQLStrict mode, allow illegal time

Next, we will explain the relevant contents in detail.

MySQLTime type

MySQLThere are three types of time:

  • DATE:It is used when only the date is included but not the time,MySQLThe format will be converted toYYYY-MM-DD, the legal scope is1000-01-01 - 9999-12-31
  • DATETIME:For include date+In time, the format isYYYY-MM-DD HH:MM:SS, the legal scope is1000-01-01 00:00:00 - 9999-12-31 23:59:59
  • TIMESTAMP:For include date+In time, the format isYYYY-MM-DD HH:MM:SS, the legal scope is1997-01-01 00:00:01 - 2038-01-19 03:14:07 UTC

At the same time,DATETIMEandTIMESTAMPThey all support one6Bit microsecond data support, format:YYYY-MM-DD HH:MM:SS[.fraction], the legal scope is.000000 - .999999

DATETIMEandTIMESTAMPIt also provides data that is automatically initialized and updated to the current date and time.

aboutTIMESTAMPType,MySQLData values are converted toUTCThe standard time is used for storage, and the current time is used for reading. If your time zone has not changed, the value is the value you store. If you change the time zone, the value you read will change. This feature won’t workDATETIMETake effect.

Viewing time zones

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

You can see that the time zone currently set isSYSTEM, which is consistent with the operating system, and the time zone of the system isCST (China standard time), you can also see that yesEast 8 (+ 0800)

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

So we input1970-01-01 08:00:00MySQL will be corrected to1970-01-01 00:00:00, which becomes an illegal value.

Illegal time value

For illegal time values, for different time types,MySQLIt will be converted to the appropriate value:0000-00-00 or 0000-00-00 00 00:00:00

For example, the month is1-12Month, when you try to insert2019-13-01 00:00:00Will be corrected to0000-00-00 00:00:00Because it doesn’t exist13Month, is illegal.

Strict mode

When we insert an illegal time value, it will be corrected, but in strict mode, it will not insert data, but will report an error:

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

We can adjust it by setting the modeMySQLFirst, look at theMySQLMode:

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

In this mode, the illegal time will directly report an error. We can adjust the mode toALLOW_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 validity of the date will not be fully checked, only the range of the month will be checked1-12Date is1-31。 This is suitable for processing user input, but this mode is only suitable forDATEandDATETIMEVery suitable forTIMESTAMP, a legal value is still needed, otherwise it will be corrected to0000-00-00 00:00:00

In case of illegal value, if this mode is enabled, an error will be reported; if it is disabled, it will be corrected to0000-00-00 00:00:00And a warning is generated:

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

summary

There are two solutions to this problem:

  1. Adjust time to legal range
  2. adjustmentMySQLStrict mode, allow illegal time

caseSummary

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

View reason discovery is set to:

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

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

Check the official documents and find the reason:5.6.5Not supported belowdatetimeType, but you can usetimestampType.

Reference material

  1. 11.3.1 The DATE, DATETIME, and TIMESTAMP Types:https://dev.mysql.com/doc/ref…
  2. 5.1.13 MySQL Server Time Zone Support: https://dev.mysql.com/doc/ref…
  3. 5.1.11 Server SQL Modes: https://dev.mysql.com/doc/ref…