MySQL 中 TIMESTAMP和DATETIME的比较

相同点

两者都可用来表示YYYY-MM-DD HH:MM:SS[.fraction]类型的日期。

MySQL 中 一个完整的日期格式 可以分为 date 和 time部分，其中，date部分对应格式中的“YYYY-MM-DD”，time部分对应格式中的“HH:MM:SS[.fraction]”。

对于date类型字段来说，它只支持date部分，如果插入了time部分的内容，它会丢弃掉该部分的内容，并提示一个warning。

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


mysql>
mysql>
mysql>
mysql> insert into test values(1,'20231108000000');
Query OK, 1 row affected (0.02 sec)


mysql>
mysql>
mysql> insert into test values(1,'20231108150900');
Query OK, 1 row affected, 1 warning (0.01 sec)


mysql>
mysql>
mysql>
mysql> select * from test;
+------+------------+
| id   | hiredate   |
+------+------------+
|    1 | 2023-11-08 |
|    1 | 2023-11-08 |
+------+------------+
2 rows in set (0.00 sec)


mysql> show warnings;
+-------+------+-----------------------------------------------------------------------+
| Level | Code | Message                                                               |
+-------+------+-----------------------------------------------------------------------+
| Note  | 1292 | Incorrect date value: '20231108150600' for column 'hiredate' at row 1 |
+-------+------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

警告部分显示 日期值 不正确

注意： 第一个没有提示 warning 的原因是 time 部分为 0

不同点

存储方式不一样

TIMESTAMP：会根据 数据库时区 的改变而转换

DATATIME: 不做任何改变

验证

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


mysql>
mysql>
mysql> insert into test1 values(1,'20231108000000');
Query OK, 1 row affected (0.01 sec)


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


mysql> insert into test2 values(1,'20231108000000');
Query OK, 1 row affected (0.01 sec)


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


mysql>
mysql>
mysql> select * from test2;
+------+---------------------+
| id   | hiredate            |
+------+---------------------+
|    1 | 2023-11-08 00:00:00 |
+------+---------------------+
1 row in set (0.00 sec)


mysql>

两者输出是一样的。

修改当前会话的时区

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


mysql>
mysql>
mysql>
mysql> set time_zone='+0:00';
Query OK, 0 rows affected (0.01 sec)


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


mysql>
mysql> select * from test1;
+------+---------------------+
| id   | hiredate            |
+------+---------------------+
|    1 | 2023-11-07 16:00:00 |
+------+---------------------+
1 row in set (0.00 sec)


mysql> select * from test2;
+------+---------------------+
| id   | hiredate            |
+------+---------------------+
|    1 | 2023-11-08 00:00:00 |
+------+---------------------+
1 row in set (0.00 sec)

“CST”指的是MySQL所在主机的系统时间，是中国标准时间的缩写

test1中返回的时间提前了8个小时，而test2中时间则不变

存储的时间范围不一样

timestamp所能存储的时间范围为：'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'。

datetime所能存储的时间范围为：'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'

存储空间占用不同

timestamp：4字节

datetime： 8字节

总结

TIMESTAMP 和 DATETIME 存储范围，大小，存储方式不一样，。当然，对于跨时区的业务，TIMESTAMP更为合适