Mysql中timestamp和datetime的区别

Mysql数据库的时间类型有date/datetime/timestamp,其中date是日期类型，datetime和timestamp是常用的时间类型，这篇文章主要讲述这两个时间类型的数据库字段有何区别

1.《高性能Mysql》上的内容片段

• DateTime

这个类型能保存很大的值，从1001到9999年，精度为秒，它把时间封装到格式YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。

• Timestamp

Timestamp保存了1970年1月1日(格林尼治时间)已来的秒数，和UNIX时间戳相同，使用4字节保存,因此它的范围比DateTime小得多；只能表示从1970年到2038年。Mysql提供了查看时间戳的函数，以及将时间戳转换为时间的函数。

select UNIX_TIMESTAMP() //查看时间戳
select FROM_UNIXTIME(1574404965) //将时间戳转换为时间
复制

此处稍微引申一点，时间戳是什么，看看定义

时间戳是指格林威治时间1970年01月01日00时00分00秒(北京时间1970年01月01日08时00分00秒)起至现在的总秒数。通俗的讲， 时间戳是一份能够表示一份数据在一个特定时间点已经存在的完整的可验证的数据。它的提出主要是为用户提供一份电子证据， 以证明用户的某些数据的产生时间。在实际应用上， 它可以使用在包括电子商务、 金融活动的各个方面， 尤其可以用来支撑公开密钥基础设施的 “不可否认” 服务。

需要理解的是在地球的任一地区的同一时刻，他们的时间戳都是相同的。换算成该时区的时间时，才会出现差异。这个也可以在后面进行验证。下面继续看Timestamp。我们知道4字节的范围是-2的31次方~2的31次方，可以通过

select from_unixtime(2147483600)//结果2038-01-19 11:13:20

这里我发现准确的2的31次方是2147483648超出了Timestamp的范围查询结果为null，
所以我稍微缩小了一点范围。
复制

Timestamp的显示依赖于时区，Mysql服务器，操作系统，以及客户端连接都有时区设置。因此，存储值为0的Timestamp在美国东部显示为“1969-12-31 19:00:00”。如果是多个时区的存储和访问，timestamp和datetime的行为很不一样。前者提供的值和时区有关系，后者是文本标识日期和时间。

2.验证Timestamp和Datetime跨时区的表现行为

首先我在一台CentOs机器上用docker启动两个装有Mysql的容器，如下

[root@VM_0_2_centos ~]# docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
d1c51091dc6d        mysql:5.7           "docker-entrypoint.s…"   22 hours ago        Up 19 hours         33060/tcp, 0.0.0.0:3307->3306/tcp   mysqlslave
3d8a5f607902        mysql:5.7           "docker-entrypoint.s…"   23 hours ago        Up 19 hours         0.0.0.0:3306->3306/tcp, 33060/tcp   mysql
复制

其中在启动服务之前，我分别修改了服务器的时区，

对于mysql这个容器,

[mysqld]
default-time_zone = '-8:00'  西8区
server-id=10 
log-bin=mysql-bin
复制

对于mysqlslave这个容器

[mysqld]
default-time_zone = '+8:00'  东8区
server-id=11
log-bin=mysql-bin
relay_log=mysql-relay-bin
复制

改完配置后，对两台服务器设置了主从(不清楚的话可以看我之前的文章Mysql的主从搭建原理及实践)，即mysqlslave这台机器会从mysql同步数据。配置完主从后，在主库创建一个表

CREATE TABLE `test_time` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time1` datetime DEFAULT NULL,
  `time2` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
复制

这时会发现从库也同步了该表。在主库和从库同时执行以下语句:

select now();
select UNIX_TIMESTAMP();

//主库（时区:UTC-8）
2019-11-24 18:43:42
1574649822

//从库（时区:UTC+8）
2019-11-25 10:43:47
1574649827

可以看到时间差了16小时，时间戳是相同的(有误差是因为两个代码不是同时执行,我手动触发的)
复制

接着，我们向主库插入以下数据

insert into test_time(time1,time2) values(now(),now())
复制

查看主库和从库

主库（UTC-8）:

从库（UTC+8）:

可以看到，对于datetime类型的time1,数据库同步时，西八区的数据和东八区的数据保持一致，因为是文本格式保存的，对于timestamp类型的time2,数据库同步时,因为保存的是时间戳，西八区的数据会自动转换为东八区的数据，因此数据会发生改变。

上述情景展示了国际化业务中，异地部署数据库，并相互同步时会发生的情况，实际上我们期望的使用方式是美国发生业务，代码中存入当地时间即可，存入数据库使用timestamp字段类型，同步至中国服务器，该字段会自动转为东八区的时间，部署在中国的业务代码再讲数据读出，正确展示。整个步骤依赖于数据库的时区正确配置，以及业务代码和数据库时区相互配合。

此外对于《高性能Myqsl》文中说到的，时区展示依赖于客户端时区、服务端时区。可以通过以下命令查看时区 show VARIABLES like"%time_zone%"

可以通过 settime_zone='+8:00';
修改本连接的时区为+8.00，这个配置在重启连接会失效，但是本连接查看时间，发现已经从西8区修改为了东8区,显示的时间和slave机器上的相同。

3.Timestamp和Datetime如何选

高性能Mysql给了我们充分的建议，首先考虑时间范围，timestamp的时间范围是1970-2038年，如果时间范围允许的话尽量用timestamp，因为占用内存小，节约空间。同时timestamp是会受时区影响的，在国际化业务中需要明确时区设置才能正确使用。时间范围超过了timestamp的范围，或者业务和时区无关，可以考虑选择datetime(当然datetime也有局限性，例如三国时期的人物生日都在公元前，datetime也是无法涵盖的)。