Timestamp and datetime in MySQL

In MySQL, time is the type we use most. How do you choose the time field type when creating a table? Some people will say timestamp, while others will say datetime. So how do we choose? What's the difference between them? Let's have a look today.

1、 How does MySQL represent the current time?

In fact, there are many ways of expression, which are summarized as follows:

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

NOW()

LOCALTIME

LOCALTIME()

LOCALTIMESTAMP

LOCALTIMESTAMP()

2、 Comparison between TIMESTAMP and DATETIME

A complete date format is as follows: YYYY-MM-DD HH: MM: SS [. fraction], which can be divided into two parts: the date part and the time part. The date part corresponds to "YYYY-MM-DD" in the format, and the time part corresponds to "HH: MM: SS [. fraction]" in the format. For the date field, it only supports the date part. If the time part is inserted, it will discard the part and prompt a warning.

As follows:

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 | +------+------------+ rows in set (0.00 sec)

Note: The first reason for not prompting warning is that its time part is all 0

Similarities between TIMESTAMP and DATETIME:

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

Differences between TIMESTAMP and DATETIME:

1> The storage methods of the two are different

For TIMESTAMP, it converts the time inserted by the client from the current time zone to UTC (world standard time) for storage. When querying, it will be converted to the current time zone of the client for return.

For DATETIME, no change is made, and the input and output are basically the same.

Next, let's verify

First, create two test tables, one in timestamp format and the other in 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)

Both outputs are the same. Second, modify 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 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 system time of the host running MySQL. It is the abbreviation of China Standard Time UT+8:00. The results show that the time returned in test is 8 hours ahead of schedule, while the time in test1 is unchanged. This fully verifies the difference between the two.

2> They can store different time ranges

The time range that timestamp can store is: '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'.

The datetime can be stored from '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'.

Conclusion: TIMESTAMP and DATETIME are not very different except for the storage range and storage mode. Of course, TIMESTAMP is more appropriate for cross time zone services.