Timestamp and datetime are the same and different

The same point of Timestamp and datetime:

1> Both can be used to represent the date of the Yyyy-mm-dd hh:mm:ss[.fraction] type.

Different points of timestamp and datetime:

1> the two are stored in a different way.

For timestamp, it stores the time that the client inserts from the current time zone into UTC (World standard Time). Query, it is converted to the client's current time zone for return.

And for datetime, do not make any changes, basically is the original input and output.

Next, let's verify

First, create two test tables, one using the timestamp format and one using the 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 |
+------+---------------------+
row in Set (0.01 sec)

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

Second, modify the time zone for the current session

Mysql> Show variables like '%time_zone% '; 
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| Time_zone        | SYSTEM |
+------------------+--------+
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 |
+------+---------------------+
row in Set (0.00 sec)

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

The above "CST" refers to the MySQL host's system time, is the Chinese standard time abbreviation, China Standard Times ut+8:00

The result shows that the time returned in test is 8 hours ahead of time, while test1 is unchanged. This fully validates the difference between the two.

2> the time range that can be stored is different

Timestamp can store the time range: ' 1970-01-01 00:00:01.000000 ' to ' 2038-01-19 03:14:07.999999 '.

The time range that DateTime can store is: ' 1000-01-01 00:00:00.000000 ' to ' 9999-12-31 23:59:59.999999 '.

Summary: Timestamp and datetime are not much different except for storage scope and storage style. Of course, timestamp is more appropriate for business across time zones.

III. automatic initialization and updating of timestamp and datetime

First, let's take a look at the following actions

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 |
+------+---------------------+
row in Set (0.00 sec)

mysql> Show create table Test\g
*************** 1. Row ***************************
       table:test
Create table:create Table ' test ' (
  ' id ' int (one) DEFAULT NULL ,
  ' hiredate ' timestamp not NULL default current_timestamp on UPDATE current_timestamp
) engine=innodb DEFAULT C Harset=latin1
Row in Set (0.00 sec)

Does it look strange, I didn't insert the HireDate field, its value was automatically modified to the current value, and when I created the table, I did not define the "DEFAULT Current_" shown in the "Show create TABLE Test\g" result. TIMESTAMP on UPDATE current_timestamp ".

In fact, this feature is automatic initialization and automatic updating (Automatic initialization and updating).

Automatic initialization means that if there is no explicit assignment to the field (for example, the HireDate field in the previous example), it is automatically set to the current system time.

Automatic Updates means that if you modify other fields, the value of that field is automatically updated to the current system time.

It is related to the "Explicit_defaults_for_timestamp" parameter.

By default, the value of this parameter is off, as follows:

Mysql> Show variables like '%explicit_defaults_for_timestamp% ';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | Off   |
+---------------------------------+-------+
row in Set (0.00 sec)

Let's take a look at the official file description:

By default, the "TIMESTAMP" column has both default Current_timestamp and on UPDATE current_timestamp if neither is SP Ecified explicitly.

Most of the time, this is not what we want, how to disable it.

1. Set the value of "Explicit_defaults_for_timestamp" to ON.

2. The value of "Explicit_defaults_for_timestamp" is still off, and there are two ways to disable

1> the column with the default clause to specify a value

2> Specifies the Null property for the column.

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 () DEFAULT NULL, ' H Iredate ' timestamp null default null ' Engine=innodb DEFAULT charset=latin1 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 () DEFAULT NULL, ' H Iredate ' timestamp not NULL default ' 0000-00-00 00:00:00 '] engine=innodb DEFAULT charset=latin1 row in Set (0.00 sec) 

Prior to MySQL version 5.6.5, Automatic initialization and updating were only applicable to timestamp, and a table that allowed a maximum of one timestamp field to adopt the attribute. Starting with the MySQL 5.6.5, Automatic initialization and updating apply to both timestamp and datetime, without limiting the number.

Reference:

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

3. http://www.2cto.com/database/201308/233832.html