This is the 31st day of my participation in the August More Text Challenge

MySQL > select * from time; select * from time; select * from time; Some people say timestamp, some people say datetime, so how do we choose, and what’s the difference? Let’s take a look today.

MySQL > select * from ‘MySQL’;

In fact, there are many expressions, summarized as follows:

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

NOW()

LOCALTIME

LOCALTIME()

LOCALTIMESTAMP

LOCALTIMESTAMP()

Second, about TIMESTAMP and DATETIME comparison

A complete date format is as follows: YYYY-MM-DD HH:MM:SS[. Fraction], which can be broken down into two parts: yyyY-MM-DD HH:MM:SS The date part corresponds to yyyY-MM-DD and the time part corresponds to HH:MM:SS[.fraction]. For the date field, it only supports the Date part, and if the time part is inserted, it discards that part and prompts a warning.

As follows:

mysql> create table test(id int,hiredate date); Query OK, 0 rows affected (0.01sec) 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.01sec) mysql> select * from test; +------+------------+ | id | hiredate | +------+------------+ | 1 | 2015-12-08 | | 1 | 2015-12-08 | +------+------------+ rows in set (0.00 SEC)Copy the code

Note: The first one is not warning because its time part is 0

TIMESTAMP and DATETIME similarities:

Both can be used to represent dates of type YYYY-MM-DD HH:MM:SS[. Fraction].

TIMESTAMP and DATETIME:

1> The storage modes are different

For TIMESTAMP, it converts the client inserted time from the current time zone to UTC (Universal Standard Time) for storage. When querying, it is returned as the current time zone of the client.

For DATETIME, no changes are made and the input and output are basically as-is.

Now, let’s verify that

First, create two test tables, one using timestamp format and one using datetime format.

mysql> create table test(id int,hiredate timestamp); Query OK, 0 rows affected (0.01sec) 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.01sec) 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 the set (0.01 SEC) mysql > select * from test1. +------+---------------------+ | id | hiredate | +------+---------------------+ | 1 | 2015-12-08 00:00:00 | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + row in the set (0.00 SEC)Copy the code

The output is the same.

Next, change the time zone of the current session

mysql> show variables like '%time_zone%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | CST | | Time_zone | SYSTEM | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + rows in the 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 the set (0.00 SEC) mysql > select * from test1. +------+---------------------+ | id | hiredate | +------+---------------------+ | 1 | 2015-12-08 00:00:00 | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + row in the set (0.01 SEC)Copy the code

CST refers to the system Time of the MySQL host. It is the abbreviation of China Standard Time UT+8:00. As you can see from the results, the time returned in Test is 8 hours earlier than in Test1. This fully demonstrates the difference between the two.

2> The time range that the two can store is different

Timestamp The time range that can be stored is ‘1970-01-01 00:00:01.000000’ to ‘2038-01-19 03:14:07.999999’.

The value of datetime ranges from ‘1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’.

Summary: TIMESTAMP and DATETIME are not very different except the storage scope and storage mode. Of course, TIMESTAMP is more appropriate for business across time zones.

Automatic initialization and update of TIMESTAMP and DATETIME

First, let’s look at the following operation

mysql> create table test(id int,hiredate timestamp); Query OK, 0 rows affected (0.01sec) 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 the 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 row in set (0.00 SEC)Copy the code

If it seems odd, I didn’t insert the hiredate field, it was automatically changed to the current value, and when I created the table, I also did not define “DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP” as shown in the result of “show create table test\G”.

In fact, this feature is Automatic Initialization and Updating. Automatic initialization means that if there is no explicit assignment to the field (such as the hiredate field in the example above), it is automatically set to the current system time.

Automatic update means that if other fields are modified, the value of this 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 the set (0.00 SEC)Copy the code

Let’s take a look at the official document:

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, this is not what we want, how do you disable it?

1. Set explicit_DEFAULts_for_TIMESTAMP to ON.

2. The value of “explicit_defaults_for_TIMESTAMP” is still OFF, but there are two ways to disable it

1> Specify a DEFAULT value for this column with the DEFAULT clause

2> Specify a NULL attribute for this column.

As follows:

mysql> create table test1(id int,hiredate timestamp null); Query OK, 0 rows affected (0.01 SEC) mysql > show create table test1 \ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1. The row *************************** Table: test1 Create Table: CREATE TABLE `test1` ( `id` int(11) DEFAULT NULL, 'hiredate' 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. The 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 row in set (0.00 SEC)Copy the code

Prior to MySQL 5.6.5, Automatic Initialization and Updating only applies to TIMESTAMP and is allowed for up to one TIMESTAMP field in a table. Starting from MySQL 5.6.5, Automatic Initialization and Updating applies to both TIMESTAMP and DATETIME without limiting the amount.

Reference:

1. Dev.mysql.com/doc/refman/…

2. Dev.mysql.com/doc/refman/…

3. www.2cto.com/database/20…