MySQL：datetime与timestamp的区别及使用选择

datetime与timestamp的区别

1、存储空间不同
在MySQL，timestamp在内部存储为整型，占用4个字节，而datetime占用8个字节。

2、存储方式不同
timestamp存储时，会从插入时间的客户端时区转换为UTC（世界标准时间）时间存储，而datetime不会做时间的转换直接存储。

3、存储的时间范围不同

timestamp：UTC时间，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

4、自动更新
在同一个表里，允许有一个字段自动更新时间。如

CREATE TABLE `tb` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `mydatetime` datetime DEFAULT NULL,
 `mytimestamp` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

其中，mytimestamp为自动更新时间，ON UPDATE CURRENT_TIMESTAMP。

使用选择

在datetime和timestamp这几个不同里，存储方式和时间范围的不同会影响到我们对两个类型的选择。

timestamp始终会存储为UTC时间，也就是说它是一个固定时间，只是在不同的时区表示不同而已。而datetime则是一个静态的时间，与时区无关。

在使用选择上需要知道不同的存储方式会带来的问题

问题一

在跨时区做数据迁移，datetime类型的数据不能自动修正，需要我们使用其他方式做数据修正。因为它是一个静态的时间。

示例

CREATE TABLE `time_test` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `mydatetime` datetime DEFAULT NULL,
 `mytimestamp` timestamp NULL DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

查看系统时区

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name  | Value |
+------------------+--------+
| system_time_zone | CST  |
| time_zone    | SYSTEM |
+------------------+--------+

往time_test插入数据

mysql>INSERT INTO time_test (mydatetime,mytimestamp) VALUES (NOW(),NOW());
mysql>SELECT * FROM time_test;
+----+---------------------+---------------------+
| id | mydatetime       | mytimestamp      |
+----+---------------------+---------------------+
| 1 | 2017-12-08 22:14:56 | 2017-12-08 22:14:56 |
+----+---------------------+---------------------+

修改时区

mysql>SET time_zone='+9:00';
mysql>SELECT * FROM time_test;
+----+---------------------+---------------------+
| id | mydatetime       | mytimestamp      |
+----+---------------------+---------------------+
| 1 | 2017-12-08 22:14:56 | 2017-12-08 23:14:56 |
+----+---------------------+---------------------+

把时区修改为“+9:00”，即东九区，mytimestamp时间显示比之前多了一个小时。而mydatetime没有变化

继续插入一行数据

mysql>INSERT INTO time_test (mydatetime,mytimestamp) VALUES (NOW(),NOW());
mysql>SELECT * FROM time_test;
+----+---------------------+---------------------+
| id | mydatetime       | mytimestamp      |
+----+---------------------+---------------------+
| 1 | 2017-12-08 22:14:56 | 2017-12-08 23:14:56 |
| 2 | 2017-12-08 23:17:53 | 2017-12-08 23:17:53 |
+----+---------------------+---------------------+

注意，此时为东九区，mydatetime插入的也是东九区当时的时间，而第一行数据仍然为旧的数据。

修改为东八区

mysql>SET time_zone='+8:00';
mysql>INSERT INTO time_test (mydatetime,mytimestamp) VALUES (NOW(),NOW());
mysql>SELECT * FROM time_test;
+----+---------------------+---------------------+
| id | mydatetime       | mytimestamp      |
+----+---------------------+---------------------+
| 1 | 2017-12-08 22:14:56 | 2017-12-08 22:14:56 |
| 2 | 2017-12-08 23:17:53 | 2017-12-08 22:17:53 |
+----+---------------------+---------------------+

此时，mytimestamp两个时间都显示为东八区的时间，而mydatetime还是原来插入的时间。

问题二

timestamp所能表示的时间范围小：UTC时间，1970-01-01 00:00:01.000000到 2038-01-19 03:14:07.999999

解决

timestamp所能表示的时间范围小是MySQL设计上决定的，我们在应用层解决不了。datetime在跨时区的问题我们可以在应用层解决。

解决的思路和存储timestamp的方式是一样的，在应用层把datetime的时间统一转为UTC，然后我们再存进MySQL。如果需要在不同时区显示，只需要在应用层把UTC时间转换为时区时间即可。