MySQL 时区全解，datetime 和 timestamp 区别，GMT、UTC、CST、东八区分别指什么？

发表评论 阅读(12637)

阿里巴巴 Java 开发手册中有条规范

【强制】表必备三字段:id, gmt_create, gmt_modified。

其中 id 必为主键，类型为 unsigned bigint、单表时自增、步长为 1。gmt_create, gmt_modified 的类型均为 date_time 类型，前者现在时表示主动创建，后者过去分词表示被动更新。

其中后面两个时间字段很有意思，能展开学习的东西非常多。比如：

  • 建表日期时间字段为啥要用 date_time ，用 timestamp 不行吗？
  • 日期时间属性 date_time 的长度约束作用是什么，要设置吗？
  • mysql 中让人懵逼的 GMT、UTC、CST、东八区分别是什么？
  • 数据库时间和当前时间相差 8 小时问题是怎么产生的，相差 13 小时呢？
  • JDBC 设置的时区有什么作用

这两天工作碰到这样一个问题：被告知，某个用户添加设备后页面显示的添加时间不对，和正确时间相差 13 个小时。刚开始接到问题有点懵逼，时间不对，怎么其他功能涉及的时间是准确的，就这里不对。

先查数据库时区，查出来如下：

show variables like '%time_zone%'

image-20211116141139708

JDBC 配置时区

serverTimezone=Asia/Shanghai

表里时间字段如下

`create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '添加时间',

因为时区产生这个小问题，深入学习，摸清楚了很多忽略的东西，今天一起来做个分享。再解决问题之前，先看上面那几个问题。

1、datetime和timestamp的区别是什么，定义字段时用哪个？还有它的长度Length约束作用是什么？

区别很多，首先占用空间就不一样，timestamp占用 4 个字节，32位，datetime占用空间更大，是 8 个字节，那建表时，是不是用timestamp更好呢？

并不是，上面的只是datetime小问题，但timestamp存在两个大问题。

timestamp存在 2037 终结时间，它没办法存 2037 年以后的时间。我们日常把timestamp叫时间戳，其实很贴切，它底层就是用 int 存储时间，但 int 的问题是长度不够，只有 4 个字节，最大值 2147485647 只能表达到 2037 年。

大家可以创建个timestamp时间，然后将时间调到 2038 年试试，数据库会存储失败

还有就是时区问题，timestamp存储的时间会因为数据库时区不一样，查询取出来的值不一样。比如，我在东八区存入一个时间，在东九区查询取出，时间竟然不是原来的时间。

而datetime没上面的问题，可以接受 2038 年以后的时间，存取时间是一样的，存入多少，取出来就是一样。

所以，项目中还是建议用datetime。

再来看Length，它其实代表毫秒的位数，一般有三档，0，3，6，看下面例子就懂了。

Length=0 2021-11-12 17:55:05
Length=3 2021-11-16 15:52:38.081
Length=6 2021-11-16 15:53:01.922523

如果没特殊需求，一般这里长度设置成 0 就行，方便阅读。

再来看上面的默认值CURRENT_TIMESTAMP，顾名思义，如果 JDBC 没传入值，默认赋值当前时间。但如果用 Navicat 建表，你会发现这个CURRENT_TIMESTAMP默认值找不到。

这个问题我第一次建表时也碰到过，当时还找了不少办法，结果发现只要将上面的文本拷贝粘贴到默认值选项框就行了。

image-20211116162035620

下面的选项ON UPDATE CURRENT_TIMESTAMP勾上，SQL 表达式如下：

`create_time` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '创建时间',

它的作用是用来更新时间，一旦一行数据中其他任意数据项发生更新或者说变化，这个字段都会获取当前最新时间并设置，这个字段最常用来设置上面阿里规范提到的修改时间（更新时间）gmt_modified。

2、GMT、UTC、CST、东八区

这些名词估计大家常看到，刚才我查询数据库时区时也看到显示CST。

太多概念容易绕混，按照我的经验，这里不用细分，可以分成两类，GMT、UTC 和 CST、东八区。

GMT： 格林威治标准时间，英国伦敦格林威治定为 0° 经线开始的地方，地球每 15° 经度 被分为一个时区，共分为 24 个时区，相邻时区相差一小时。

UTC：世界协调时间，经严谨计算得到的时间，精确到秒，误差在 0.9s 以内， 是比 GMT 更为精确的世界时间。

所以，这两者其实可以划等号，我们平时关注 GMT 就行，因为它更知名。

东八区就是中国标准时间，它也是相对 GMT 而言的。所以，我们平时碰到相差 8 小时问题，就是因为中国标准时间和 GTM 相差 8 个时区，也就是相差 8 小时。

数据库中表达中国标准时间+8:00，GMT 是+00:00。如果想暂时修改数据库的时区，命令如下：

set time_zone='+08:00';

再来看 CST，它更复杂

表示从 1970 年 1 月 1 日 00:00:00 到现在所经历的秒数

刚才我查询数据库的时区就是这个，我们再来看这张图

image-20211116141139708

需要注意的是time_zone才是数据库时区，它是我们关注的。这里它指向 SYSTEM，也就是数据库服务器的系统时间（system_time_zone），而这个系统时间在这里指向 CST，所以，我们数据库时区才是 CST。并不是因为system_time_zone的值是 CST，所以数据库的时区就是 CST。

可能有点绕，对着图片多看几次

要严格区分time_zone和数据库服务器的系统时间（system_time_zone）。前者才是我们要注意的，而且我们设置数据库的时区，改变的也是它的值，与system_time_zone没关系。比如，前面提到东八区时区是中国标准时间，我们可以将这里数据库的时区设置成它。

set time_zone='+08:00';

再来查询时区

image-20211116165517969

数据库时区time_zone已经改变，直接指向东八区，也就是 GMT+8，这就是数据库的时区，但system_time_zone还是指向 CST。

数据库时区的修改我们知道了，system_time_zone是什么，由谁控制的呢？

它其实是数据库所在服务器的时区，服务器是台主机，是台电脑，我们可以用本地数据库做个测试。如果你本地安装了数据库，那修改电脑的时区，看看这里的值是否会变。像我这边 macOS 默认时区是中国标准时间

image-20211116170411551

查出来 system_time_zone 是 CST。

现在我将时区切换到日本去，可以看看电脑的时间，应该会发生变化

image-20211116170542720

再退出数据库，关闭数据库服务，再启动，也就是重启。

image-20211116170628892

再进入数据库查询

image-20211116170701236

查出来 system_time_zone 变成 JST。现在应该发现了，它是跟随数据库所在服务器的时区变化的。

再来看开始查询到的 CST，它是不是代表中国标准时间呢？是也不是，有四个国家的时区都用 CST 表示。

美国中部时间 Central Standard Time (USA) UTC-05:00 / UTC-06:00
澳大利亚中部时间 Central Standard Time (Australia) UTC+09:30
中国标准时间 China Standard Time UTC+08:00
古巴标准时间 Cuba Standard Time UTC-04:00

可以按照上面的步骤验证下，比如我将笔记本电脑时区修改到美国中部（重复上面步骤）

image-20211116171204596

查出来的时区也是 CST。

image-20211116171214576

所以，如果你的数据库时区是 CST（注意这句话），而插入时间也正确，那很有可能你刚好处在中国标准时间。我就是这种情况，之前项目部署在国内服务器，倒是不影响，但最近部署到国外服务器上，时区还是 CST ，但代表的含义完全变了，并不是中国标准时间。

但这没法解释为什么其他接口插入的时间是对的。

接下来内容太多（修改数据库时区、JDBC 时区参数、阿里巴巴规范、总结），这篇文章实在太长，留到下篇吧。

MySQL 时区全解，如何修改数据库时区，JDBC 时区不一致时怎么办？

本文由老郭种树原创，转载请注明：https://guozh.net/mysql-time-zone-1/

相关文章推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注