MySQL notes on time setting

Does time really exist? There is a view that time is only a concept conceived by humans, and it is a standard used to measure changes in things. For the database, time goes hand in hand with data. Look into the MySQL time vortex.

1. Time type field

MySQL time type field:

image.png

The following easily overlooked content:

TIMESTAMP data saving method:
MySQL converts the TIMESTAMP value from the current time zone to UTC for storage, and returns from UTC to the current time zone for retrieval.
(This does not apply to other types, such as DATETIME.) By default, the current time zone of each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains unchanged, the same value stored will be returned. If you store a timestamp value, and then change the time zone and retrieve the value, the retrieved value is different from the stored value. This happens because the same time zone is not used for conversion in both directions. The current time zone can be used as the value of the time_zone system variable.

image.png

The combination of TIMESTAMP and SQL_MODE
sql_mode also affects the timestamp value:

mysql> CREATE TABLE ts (         id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,         col TIMESTAMP NOT NULL     ) AUTO_INCREMENT = 1; mysql> SHOW VARIABLES LIKE  '%sql_mode%';+---------------+---------------------+| Variable_name | Value               |+---------------+---------------------+| sql_mode      | STRICT_TRANS_TABLES |+---------------+---------------------+mysql>  INSERT INTO ts (col) VALUES ('1969-01-01 01:01:10');ERROR 1292 (22007): Incorrect datetime value: '1969-01-01 01:01:10' for column 'col' at row 1 mysql> SET sql_mode="";Query OK, 0 rows affected (0.00 sec)mysql> SHOW VARIABLES LIKE  '%sql_mode%';+---------------+-------+| Variable_name | Value |+---------------+-------+| sql_mode      |       |+---------------+-------+mysql>  INSERT INTO ts (col) VALUES ('1969-01-01 01:01:10'),('2999-01-01 01:01:10');Query OK, 2 rows affected, 2 warnings (0.01 sec)Records: 2  Duplicates: 0  Warnings: 2mysql> show warnings;+---------+------+----------------------------------------------+| Level   | Code | Message                                      |+---------+------+----------------------------------------------+| Warning | 1264 | Out of range value for column 'col' at row 1 || Warning | 1264 | Out of range value for column 'col' at row 2 |+---------+------+----------------------------------------------+ mysql> SELECT * FROM TS;+----+---------------------+| id | col                 |+----+---------------------+|  1 | 0000-00-00 00:00:00 ||  2 | 0000-00-00 00:00:00 |+----+---------------------+2 rows in set (0.00 sec)

By controlling sql_mode, the timestamp limit value is exceeded or inserted, but the zero-filling method is used.
For STRICT_TRANS_TABLES, MySQL converts an invalid value to the closest valid value, and then inserts the adjusted value. If a value is missing, MySQL will insert an implicit default value for the column data type.

2. Explicit_defaults_for_timestamp time processing mechanism

image.png

It is enabled by default.
In MySQL 8.0.22, if you try to insert NULL into a column declared as TIMESTAMP NOT NULL, it will be rejected and an error will be generated.

1. When explicit_defaults_for_timestamp is disabled:

Timestamp columns that are not explicitly declared with the NULL attribute will automatically be declared with the NOT NULL attribute. It is allowed to assign NULL to such a column and set the column to the current timestamp. In MySQL 8.0.22, if you try to insert NULL into a column declared as TIMESTAMP NOT NULL, it will be rejected and an error will be generated.

If the first column in the table is not declared with NULL attributes or explicit DEFAULT or ON UPDATE attributes, it will automatically use the default CURRENT_TIMESTAMP attribute and ON UPDATE CURRENT_TIMESTAMP attribute for declaration.

TIMESTAMP If the NULL attribute or explicit default attribute declaration is not used explicitly, it is automatically declared as the default '0000-00-00 00:00:00' ("zero" timestamp)

Depending on whether the strict SQL mode or the NO_ZERO_DATE SQL mode is enabled, the default value "0000-00-00 00:00:00" may be invalid.

2.explicit_defaults_for_timestamp is enabled:

It is not possible to specify a NULL value for TIMESTAMP to set it to the current timestamp. To specify the current timestamp, set it to CURRENT_TIMESTAMP or a synonym, such as NOW().

TIMESTAMP columns that are not explicitly declared with the not NULL attribute will automatically be declared with the NULL attribute and allow null values.

The timestamp column declared with the NOT NULL attribute does not allow null values. For inserts that specify NULL for such a column, if strict SQL mode is enabled, a single-row insert will cause an error, or a multi-row insert with strict SQL mode disabled will insert '0000-00-00 00:00:00'. In any case, assigning NULL to a column will not set it to the current timestamp.

A timestamp column that is explicitly declared with the NOT NULL attribute and does not have an explicit default attribute is considered to have no default value. For inserted rows that do not specify explicit values ​​for such columns, the result depends on the SQL mode. If strict SQL mode is enabled, an error will occur. If strict SQL mode is not enabled, the column is declared with the default implicit value '0000-00-00 00:00:00' and a warning appears.

The timestamp type field will not automatically use the default CURRENT_TIMESTAMP attribute or update the CURRENT_TIMESTAMP attribute declaration. These attributes must be specified explicitly.

image.png


test:

CREATE TABLE `test1`(id bigint not null AUTO_INCREMENT COMMENT '主键ID', name varchar(20) COMMENT '主键ID',create_time TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'cr time',PRIMARY KEY(id))ENGINE=InnoDB  AUTO_INCREMENT=1 ;SHOW VARIABLES LIKE 'explicit_defaults_for_timestamp';SET  GLOBAL  explicit_defaults_for_timestamp=ON;SET  GLOBAL  explicit_defaults_for_timestamp=OFF;INSERT INTO test1(id,name,create_time) VALUES(1,'Kit',NULL);

3.mysql system configuration

System related event parameters include 3:

mysql>show global variables where Variable_name like '%time_zone%' or Variable_name like 'log_timestamp%';+------------------+--------+| Variable_name    | Value  |+------------------+--------+| system_time_zone | CST    || time_zone        | SYSTEM || log_timestamps   | UTC    |+------------------+--------+3 rows in set (0.00 sec)

1. System time zone: When the server starts, it tries to automatically determine the time zone of the host and uses it to set the system_time_zone system variable. The value will not change afterwards.

2.time_zone: The full time_zone represents the time zone in which the server is currently running. The initial time_zone value is "SYSTEM", which means that the server time zone is consistent with the system time zone.

If it is set to SYSTEM, such as MySQL function calls will call a system library to determine the current system time zone. This call may be protected by a global mutex, causing contention. The problem of high CPU usage.

Setting the session time zone will affect the display and storage of time zone-sensitive time values. This includes the value displayed by functions such as NOW() or CURTIME(), as well as the value stored in the timestamp column and the value retrieved from the timestamp column. The value of the timestamp column will be converted from the session time zone to UTC for storage, and from UTC to the session time zone for retrieval.

The session time zone setting does not affect the values ​​displayed by functions such as UTC_TIMESTAMP(), nor does it affect the values ​​in the DATE, time, or DATETIME columns. Values ​​of these data types are also not stored in UTC; the time zone only applies to them when converting from a timestamp value.

Note: mysql also provides a method to import the time zone into the mysql system library. Load the time zone information under /usr/share/zoneinfom through the mysql_tzinfo_to_sql program.

mysql> SELECT COUNT(*) FROM mysql.time_zone_name;+----------+| COUNT(*) |+----------+|        0 |+----------+

##mysql_tzinfo_to_sql tool import time zone value

shell>mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysqlmysql> SELECT COUNT(*) FROM mysql.time_zone_name;+----------+| COUNT(*) |+----------+|     1780 |+----------+

3.log_timestamps

  • This variable controls the time zone of the timestamp in the messages written to the error log and the general query log and slow query log messages written to the file.
  • It does not affect the time zone of the general query log and the writing of slow query log messages to the table (mysql.general_log mysql.slow_log).
  • The allowed log_timestamps values ​​are UTC (default) and SYSTEM (local system time zone).
    Note: UTC generally refers to Coordinated Universal Time. Coordinated Universal Time, also known as Universal Time, Universal Standard Time, and International Coordinated Time, is UTC+8 hours = Chinese time.
    Of course, the value needs to be consistent with the system record time for better management.
 

#设置时区,更改为东八区 SET GLOBAL time_zone = '+8:00';

image.png

Suggestion:
mysql configuration file my.cnf

#设置时区,更改为东八区SET GLOBAL time_zone = '+8:00';   
mysqld]log_timestamps=SYSTEMdefault-time_zone                  = '+8:00'mysql>show global variables where Variable_name like '%time_zone%' or Variable_name like 'log_timestamp%';+------------------+--------+| Variable_name    | Value  |+------------------+--------+| log_timestamps   | SYSTEM || system_time_zone | CST    || time_zone        | +08:00 |+------------------+--------+

to sum up

Learn from the time type, parameters, and system time zone how to set and use the time in MySQL.
Especially sql_mode should not be changed easily without special requirements.