Usage Summary of datetime and timestamp in MySQL

Source of the article ： Learn through http://www.bdgxy.com/

One 、MySQL How to represent the current time in ？

Actually , There are many ways of expression , The summary is as follows ：

• CURRENT_TIMESTAMP
• CURRENT_TIMESTAMP()
• NOW()
• LOCALTIME
• LOCALTIME()
• LOCALTIMESTAMP
• LOCALTIMESTAMP()

Two 、 About TIMESTAMP and DATETIME Comparison

A complete date format is as follows ：YYYY-MM-DD HH:MM:SS[.fraction], It can be divided into two parts ：date Part and time part , among ,date Part corresponds to... In the format “YYYY-MM-DD”,time Part corresponds to... In the format “HH:MM:SS[.fraction]”. about date For fields , It only supports date part , If you insert time Partial content , It discards the content of that part , And prompt a warning.

As shown below ：

mysql> create table test(id int,hiredate date);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values(1,‘20151208000000’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(1,‘20151208104400’);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from test;
±-----±-----------+
| id | hiredate |
±-----±-----------+
| 1 | 2015-12-08 |
| 1 | 2015-12-08 |
±-----±-----------+
2 rows in set (0.00 sec)

notes ： The first one didn't prompt warning The reason for this is its time Part of it is 0

TIMESTAMP and DATETIME Similarities ：

1> Both can be used to express YYYY-MM-DD HH:MM:SS[.fraction] Date of type .?

TIMESTAMP and DATETIME The difference between ：

1> They are not stored in the same way

about TIMESTAMP, It converts the time inserted by the client from the current time zone to UTC（ World standard time ） For storage . When inquiring , Convert it to the current time zone of the client to return .

And for DATETIME, Don't make any changes , It's basically the original input and output .

below , So let's verify that

First, create two test tables , A use timestamp Format , A use datetime Format .

mysql> create table test(id int,hiredate timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values(1,‘20151208000000’);
Query OK, 1 row affected (0.00 sec)

mysql> create table test1(id int,hiredate datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test1 values(1,‘20151208000000’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
±-----±--------------------+
| id | hiredate |
±-----±--------------------+
| 1 | 2015-12-08 00:00:00 |
±-----±--------------------+
1 row in set (0.01 sec)

mysql> select * from test1;
±-----±--------------------+
| id | hiredate |
±-----±--------------------+
| 1 | 2015-12-08 00:00:00 |
±-----±--------------------+
1 row in set (0.00 sec)

Both outputs are the same .

Secondly, modify the time zone of the current session

mysql> show variables like '%time_zone%'; 
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> set time_zone=‘+0:00’;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
±-----±--------------------+
| id | hiredate |
±-----±--------------------+
| 1 | 2015-12-07 16:00:00 |
±-----±--------------------+
1 row in set (0.00 sec)

mysql> select * from test1;
±-----±--------------------+
| id | hiredate |
±-----±--------------------+
| 1 | 2015-12-08 00:00:00 |
±-----±--------------------+
1 row in set (0.01 sec)

Above “CST” refer to MySQL The system time of the host , It is the abbreviation of Chinese standard time ,China Standard Time UT+8:00

It can be seen from the results ,test The return time in is advanced 8 Hours , and test1 Time does not change in . This fully verifies the difference between the two .

2> The two can store different time ranges

timestamp The range of time that can be stored is ：'1970-01-01 00:00:01.000000' To '2038-01-19 03:14:07.999999'.

datetime The range of time that can be stored is ：'1000-01-01 00:00:00.000000' To '9999-12-31 23:59:59.999999'.?

summary ：TIMESTAMP and DATETIME Except that the storage range and storage mode are different , There's no big difference . Of course , For business across time zones ,TIMESTAMP More appropriate .?

3、 ... and 、 About TIMESTAMP and DATETIME Automatic initialization and update of

First , Let's take a look at the following operations

mysql> create table test(id int,hiredate timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test(id) values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
±-----±--------------------+
| id | hiredate |
±-----±--------------------+
| 1 | 2015-12-08 14:34:46 |
±-----±--------------------+
1 row in set (0.00 sec)

mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE test (
id int(11) DEFAULT NULL,
hiredate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Does it look a little strange , I'm not right hiredate Field to insert , Its value is automatically changed to the current value , And when creating tables , I don't have a definition “show create table test\G” The result shows “ DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP”.

Actually , This feature is automatic initialization and automatic update （Automatic Initialization and Updating）.

Automatic initialization means that if the field （ For example, in the above example hiredate Field ） No explicit assignment , It is automatically set to the current system time .

Automatic update means that if other fields are modified , The value of this field will be automatically updated to the current system time .

It is associated with “explicit_defaults_for_timestamp” Parameters are related to .

By default , The value of this parameter is OFF, As shown below ：

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

Let's take a look at the description of the official file ：

By default, the first TIMESTAMP column has both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly.

A lot of times , That's not what we want , How to disable ？

1. take “explicit_defaults_for_timestamp” Is set to ON.

2. “explicit_defaults_for_timestamp” The value of is still OFF, There are also two ways to disable

???? 1> use DEFAULT Clause specifies a default value for this column

???? 2> Specify for this column NULL attribute .

As shown below ：

mysql> create table test1(id int,hiredate timestamp null);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test1\G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE test1 (
id int(11) DEFAULT NULL,
hiredate timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> create table test2(id int,hiredate timestamp default 0);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test2\G
*************************** 1. row ***************************
Table: test2
Create Table: CREATE TABLE test2 (
id int(11) DEFAULT NULL,
hiredate timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

stay MySQL 5.6.5 Before the release ,Automatic Initialization and Updating Only applicable to TIMESTAMP, And in a table , At most one is allowed TIMESTAMP The field takes this property . from MySQL 5.6.5 Start ,Automatic Initialization and Updating Apply to both TIMESTAMP and DATETIME, And there is no limit to the number .

Reference resources ：

1. http://dev.mysql.com/doc/refman/5.6/en/datetime.html

2. http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

This is about Mysql In the relevant Datetime and Timestamp This is the end of the article on the summary of the use of , More about Mysql Datetime and Timestamp Please search rookie tutorial www.piaodoo.com Previous articles or continue to browse the relevant articles below. I hope you can support rookie tutorials in the future www.piaodoo.com！