Timestamp and datetime in MySQL

2022-12-14 14:00:12

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:<p style="box-sizing: inherit; margin-top: 0px; margin-bottom: 20px; line-height: inherit; font-size: 14px; padding: 0px; color: rgb(17, 17, 17); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif; white-space: normal;">CURRENT_TIMESTAMP<p style="box-sizing: inherit; margin-top: 0px; margin-bottom: 20px; line-height: inherit; font-size: 14px; padding: 0px; color: rgb(17, 17, 17); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif; white-space: normal;">CURRENT_TIMESTAMP()<p style="box-sizing: inherit; margin-top: 0px; margin-bottom: 20px; line-height: inherit; font-size: 14px; padding: 0px; color: rgb(17, 17, 17); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif; white-space: normal;">NOW()<p style="box-sizing: inherit; margin-top: 0px; margin-bottom: 20px; line-height: inherit; font-size: 14px; padding: 0px; color: rgb(17, 17, 17); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif; white-space: normal;">LOCALTIME<p style="box-sizing: inherit; margin-top: 0px; margin-bottom: 20px; line-height: inherit; font-size: 14px; padding: 0px; color: rgb(17, 17, 17); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif; white-space: normal;">LOCALTIME()<p style="box-sizing: inherit; margin-top: 0px; margin-bottom: 20px; line-height: inherit; font-size: 14px; padding: 0px; color: rgb(17, 17, 17); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif; white-space: normal;">LOCALTIMESTAMP<p style="box-sizing: inherit; margin-top: 0px; margin-bottom: 0px; line-height: inherit; font-size: 14px; padding: 0px; color: rgb(17, 17, 17); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif; white-space: normal;">LOCALTIMESTAMP()<p style="box-sizing: inherit; margin-top: 0px; margin-bottom: 0px; line-height: inherit; font-size: 14px; padding: 0px; color: rgb(17, 17, 17); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif; white-space: normal;"> 2、 Comparison between TIMESTAMP and DATETIMEA 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 0Similarities 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 differentFor 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 verifyFirst, 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 rangesThe 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.

Knowledge sharing community for developers。

Let more developers benefit from it。

Help developers share knowledge through the Internet。

Website navigation

Recommended platform: Hosting; Database; Internet security; CSS Syntax; JavaScript Tutorial

Timestamp and datetime in MySQL

Recommended reading

Website navigation

high perspicacity

MyBatis Plus Generator generates Entity