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

阿里巴巴 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%'

《MySQL 时区全解,datetime 和 timestamp 区别,GMT、UTC、CST、东八区分别指什么?》

JDBC 配置时区

serverTimezone=Asia/Shanghai

表里时间字段如下

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

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

1、datetimetimestamp的区别是什么,定义字段时用哪个?还有它的长度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默认值找不到。

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

《MySQL 时区全解,datetime 和 timestamp 区别,GMT、UTC、CST、东八区分别指什么?》

下面的选项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 到现在所经历的秒数

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

《MySQL 时区全解,datetime 和 timestamp 区别,GMT、UTC、CST、东八区分别指什么?》

需要注意的是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';

再来查询时区

《MySQL 时区全解,datetime 和 timestamp 区别,GMT、UTC、CST、东八区分别指什么?》

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

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

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

《MySQL 时区全解,datetime 和 timestamp 区别,GMT、UTC、CST、东八区分别指什么?》

查出来 system_time_zone 是 CST。

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

《MySQL 时区全解,datetime 和 timestamp 区别,GMT、UTC、CST、东八区分别指什么?》

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

《MySQL 时区全解,datetime 和 timestamp 区别,GMT、UTC、CST、东八区分别指什么?》

再进入数据库查询

《MySQL 时区全解,datetime 和 timestamp 区别,GMT、UTC、CST、东八区分别指什么?》

查出来 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

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

《MySQL 时区全解,datetime 和 timestamp 区别,GMT、UTC、CST、东八区分别指什么?》

查出来的时区也是 CST。

《MySQL 时区全解,datetime 和 timestamp 区别,GMT、UTC、CST、东八区分别指什么?》

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

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

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

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

怎么说呢?不管有用没,还是想加上这句
老郭种树原创,转载请加上MySQL 时区全解,datetime 和 timestamp 区别,GMT、UTC、CST、东八区分别指什么?
点赞

发表评论

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