简介
众所周知,MySQL 中有两个时间类型,timestamp 与 datetime,但当在网上搜索 timestamp 与 datetime 区别时,会发现网上有不少与时区有关的完全相反的结论,主要两种:
-
timestamp 没有时区问题,而 datetime 有时区问题。原因是 timestamp 是以 UTC格式存储的,而 datetime 存储类似于时间字符串的形式;
-
timestamp 也有时区问题。
两种观点让人迷惑,那 timestamp 到底会不会有时区问题呢?
基本概念
时区
由于地域的限制,人们发明了时区的概念,用来适应人们在时间感受上的差异。比如中国的时区是东 8 区,表示为 +8:00,或 GMT+8。而日本的时区是东 9 区,表示为 +9:00,或 GMT+9,当中国是早上 8 点时,日本是早上 9 点,即东 8 区的 8 点与东 9 区的 9 点,这两个时间是相等的。
另外时间还有如下两个概念:
-
绝对时间:如 UNIX 时间戳,是 1970-01-01 00:00:00 开始到现在的秒数,如:1582416000,这种表示是绝对时间,不受时区影响,也叫纪元时 Epoch;
-
本地时间:相对于某一时区的时间,是本地时间。比如东 8 区的 2020-02-23 08:00:00,是中国人的本地时间。而在此时,日本人的本地时间是 2020-02-23 09:00:00。所以本地时间都是与某一时区相关的,脱离时区看本地时间,是没有意义的,因为你并不知道这具体是指的什么时间点。
在 Java 中,Date 对象是绝对时间,通过 SimpleDateForma t格式化出来的 yyyy-MM-dd HH:mm:ss 形式的时间字符串,是本地时间。如果 SimpleDateFormat 没有调用 setTimeZone() 显示指定时区,那么默认用的是 JVM 运行在的操作系统上的时区,我们开发机上的时区基本都是 GMT+8。
timestamp 与 datetime 区别
如下,我创建了一张表,里面 time_stamp 是 timestamp 类型,date_time 是 datetime类型,create_timestamp、create_datetime是timestamp与datetime类型,但是它们可以由数据库自动生成。
CREATE TABLE `time_test` (
`id` bigint unsigned,
`time_stamp` timestamp,
`date_time` datetime,
`create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`create_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`)
)
首先,将数据库时区设置为 +8:00,即中国的东 8 区。
然后,如下图手动插入一个固定时间的数据,以及用 now() 函数插入当前时间。
当插入完数据后,然后我们修改当前会话的时区为 +9:00,即日本的东 9 区,然后再次查看数据。
如上,定义为 timestamp 类型的列 time_stamp、create_timestamp 不管是手动插入的,还是 now() 函数插入的,东9区都比东 8 区的时间大 1 个小时。
这是正确的,说明 timestamp 类型是时区相关的。然而,定义为 datetime 类型的date_time、create_datetime 字段,时间都没有变化,这说明 datetime 类型是时区无关的。
结论
timestamp 在存储上是包含时区的,而 datetime 是不包含时区。说明网上的第一种说法是对的。
再看个例子
我们将东 8 区的的 2020-02-23 08:00:00 转换为 UNIX 时间戳(绝对时间),再插入数据库试试。
如下,使用 Linux 的 date 命令转换时间串为 UNIX 时间戳:
如上,定义为 timestamp 类型的列 time_stamp、create_timestamp 不管是手动插入的,还是 now() 函数插入的,东9区都比东 8 区的时间大 1 个小时。
这是正确的,说明 timestamp 类型是时区相关的。然而,定义为 datetime 类型的date_time、create_datetime 字段,时间都没有变化,这说明 datetime 类型是时区无关的。
结论
timestamp 在存储上是包含时区的,而 datetime 是不包含时区。说明网上的第一种说法是对的。
再看个例子
我们将东 8 区的的 2020-02-23 08:00:00 转换为 UNIX 时间戳(绝对时间),再插入数据库试试。
如下,使用 Linux 的 date 命令转换时间串为 UNIX 时间戳:
$ "date" --date="2020-02-23 08:00:00 +08:00" +%s
1582416000
然后用 MySQL 的 from_unixtime() 函数,将 UNIX 时间戳转换为 MySQL 时间类型来插入数据。
如上,查询出来的时间也是东 9 区的 9 点,时间也是正确的。
为什么网上又说 timestamp 类型存在时区问题?
我发现网上说 timestamp 有时区问题,都是应用端插入数据,然后到数据库中去看,结果发现时间不一样。因此我打算在 Java 中写个 Demo 试一下,看能不能重现这个问题。
首先,下面是 Java 中 Entity 的定义,与上面的 time_test 表对应。注意,这里面时间属性都是用 Date 类型定义的,如下:
然后,我写了两个接口 /insert 与 /queryAll 来插入与查询数据,如下:
我把数据库的时区设置为 +09:00 时区,即日本的东 9 区,如下:
然后,调用 /insert 接口插入数据。注意,我接口传入的时间是东 8 区的 8 点,如下:
插入完成后,去数据库中查询一把,如下:
可以看到,time_stamp 字段时间是 9 点。且我已将数据库时区设置为东 9 区,东 9 区的 9 点与东 8 区的 8 点,这两个时间实际是相等的,因此时间数据没错。
用 /queryAll 接口将数据查询出来,如下:
timeStamp 属性是 1582416000000,这是毫秒级的时间缀,秒级则是 1582416000,对应是东 8 区的 2020-02-23 08:00:00,时间数据也没错。
然后,我又将 MySQL 时区修改回 +8:00,并重启我们的 Java 应用,如下:
再查询一下数据,如下:
timeStamp 属性还是 1582416000000,时间没有变化,这也是正确的。
那为什么网上会说 timestamp 存在时区问题?
经过一翻查看,我发现他们都提到了 JDBC 的 serverTimezone,会不会是这个配置错误导致的呢?就先试试吧。
如图,我把数据库时区修改回 +9:00 时区,然后故意把 JDBC 的 URL 上的 serverTimezone 配置为与数据库不一致的 GMT+8 时区,然后重启 Java 应用,如下:
url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8
其中 GMT%2B8 就是 GMT+8,因为在 URL 上需要 urlencode,所以就变成了GMT%2B8。
重新插入数据。注意,插入的时间还是东 8 区的 8 点,如下:
然后,我再到数据库中查询一把,如下:
time_stamp 时间竟然是 8 点!要知道我们虽然插入的是东 8 区的 8 点,但当前会话可是东 9 区的,东 8 区的 8 点等于东 9 区的 9 点,所以正确显示应该为 9 点才对,时间差了 1 小时!
然后,我又调用 /queryAll 接口查询了一把,想看看 MyBatis 查询出来的时间数据对不对,如下:
可以看到 timeStamp 是 1582416000000,秒级是 1582416000,这个时间就是东 8 区的 8 点,东 9 区的 9 点啊!
查询出来的时间竟然是正确的,为什么?
serverTimezone 的本质
为了找出问题所在,我调试了一下 MySQL 的 JDBC 驱动代码,终于弄明白了原因。
主要可以看看如下这几点:
1. MySQL 驱动创建连接后,会调用 com.mysql.jdbc.ConnectionImpl#configureTimezone() 来配置此连接的时区。如果配置了 serverTimezone,则会使用 serverTimezone 配置的时区。如果没有配置,会去取数据库中的 time_zone 变量。
这就是为什么我们没有配置 serverTimezone 变量时,结果也是正确的。
//若使用普通驱动,使用此方法配置mysql连接的时区
com.mysql.jdbc.ConnectionImpl#configureTimezone()
//若使用cj驱动,使用此方法配置mysql连接的时区
com.mysql.cj.protocol.a.NativeProtocol#configureTimezone()
2. 调用 JDBC 的 setTimestamp() 方法时,实际调用的是 com.mysql.cj.jdbc.ClientPreparedStatement#setTimestamp()。
这里面会根据 serverTimezone 指定的时区,将对应的 timestamp 对象转换为 serverTimezone 指定时区的本地时间字符串。
3. 执行 SQL 语句时,会执行 com.mysql.cj.jdbc.ClientPreparedStatement#execute()。
这里面 sendPacket 变量保存着真实会发送到 MySQL 的 SQL 语句。
注意:看的是 8.0.11 版本 mysql-connector-java 驱动源码,不同版本代码会稍有差异。比如 5.2.16 版本驱动,jdbc url 上需要同时配置这两个配置 useTimezone=true&serverTimezone=GMT%2B8,且 setTimestamp() 对应的是 com.mysql.jdbc.PreparedStatement#setTimestampInternal方法。
原理总结
MySQL 驱动在发送 SQL 前,会将 JDBC 中的 Date 对象参数根据 serverTimeZone 配置的时区转化为日期字符串后,再发送 SQL 请求给 MySQL server。同样,在 MySQL Server 返回查询结果后,结果中的日期值也是日期字符串。MySQL 驱动会根据 serverTimeZone 配置的时区,将日期字符串转化为 Date 对象。
因此,当 serverTimeZone 与数据库实际时区不一致时,会发生时区转换错误,导致时间偏差。
-
比如 SQL 参数是一个 Date 对象,时间值是东 8 区的 2020-02-23 08:00:00。注意它里面存储的可不是 2020-02-23 08:00:00 这个字符串,它是 Date 对象(绝对时间),只是我用文字表达出来是东8区的2020-02-23 08:00:00;
-
然后,由于 serverTimeZone 配置的是东 8 区,MySQL 驱动会将这个 Date 对象转为 2020-02-23 08:00:00。注意,这时已经是字符串了。然后,再将 SQL 发送给 MySQL。注意,这里的 SQL 里面已经将 Date 参数替换为 2020-02-23 08:00:00 了,因为 Date 对象本身是无法走网络的;
-
然后,MySQL 数据库接收到这个时间字符串 2020-02-23 08:00:00 后。由于数据库时区配置是东 9 区,它会认为这个时间是东 9 区的,它会以东 9 区解析这个时间字符串。这时,数据库保存的时间是东 9 区的 2020-02-23 08:00:00,也就是东 8 区的 2020-02-23 07:00:00,保存的时间就偏差了 1 个小时。
那么问题来了:查询结果里的时间为什么又对了呢?
因为查询结果返回了东 9 区的时间字符串,而 Java 应用又将其理解为是东 8 区的时间,负负得正了!
将 serverTimezone 与 MySQL 时区保持一致
那么,如果我们将 serverTimezone 配置改正确,即与数据库保持一致时,应该查询到的时间就会是错的,会少 1 个小时。
JDBC URL 中使用与数据库一样的东 9 区 GMT+9,如下:
url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B9&useUnicode=true&characterEncoding=utf8
其中的 GMT%2B9,即是 GMT+9。
然后,重启 Java 应用再查询一把看看,结果如下:
返回的是毫秒级时间戳 1582412400000,秒级就是 1582412400。使用 Linux 的 date命令转换为时间字符串形式:
$ "date" --date="@1582412400" +"%F %T %z"
2020-02-23 07:00:00 +0800
看到没,它是东 8 区的 7 点,刚好差了 1 个小时。
所以,使用 MySQL timestamp 类型时,对于 Java 应用一定要保证 JDBC URL 中的 serverTimezone 与数据库中的时区配置是一致的。
另外一点是,当没有配置 serverTimezone 时,MySQL 驱动会自动读取 MySQL Server中配置的时区,这里面也有坑。
MySQL 驱动自动读取数据库时区的坑
MySQL 安装好后默认时区是 SYSTEM。而 SYSTEM 指的是 system_time_zone 变量的时区,如下:
当 MySQL 驱动读到 time_zone 变量是 SYSTEM 时,会再去读取 system_time_zone 变量。
而 system_time_zone 对于国内来说,默认是 CST。这是一个混乱的时区,是 4 个不同时区的缩写,如下:
对于 Linux 或 MySQL,会认为 CST 是中国标准时间 (+8:00)。但 Java 却认为 CST 是美国标准时间 (-6:00) 注:可能和 Java 运行在 Windows 中有关
如下,Linux 中 CST 等于 +0800,即中国时区:
$ "date" +"%F %T %Z %z"
2021-09-12 18:35:49 CST +0800
如下,Java 中 CST 等于 -06:00,美国时区:
因此,MySQL 驱动取到 CST 这个时区值时,它会以为这是 -6:00 时区,但 MySQL 却理解为 +8:00 时区。
因此 MySQL 时区一定不要配置为 CST,而要配置为具体的时区,如 +8:00。但如果MySQL 时区为 CST 且不可修改的情况下,一定要配置 JDBC 的 serverTimezone 为清晰的时区(如 GMT+8)。
Entity 中日期属性是 String 呢?
我们将 Entity 对象中的时间属性改为 String(不推荐),如下:
然后也写两个接口,/insert2 与 /queryAll2,如下:
然后插入数据。注意,这时我是直接将无时区的 8 点作为参数给到 SQL 的,如下:
然后再查询一把,如下:
如上所示,time_stamp 字段值是 8 点,但此时数据库时区是东 9 区,所以这是东 9 区的 8 点。
然后,将数据库与 JDBC 中 serverTimezone 都改为东 8 区,改完后重启Java应用。如下:
url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8
再次插入数据,参数还是无时区的 8 点,如下:
再查询一把,如下:
如上所示,time_stamp 字段值是 8 点,但现在数据库时间是东 8 区,所以这是东 8 区的 8 点。
然后,再将 JDBC URL 上的 serverTimezone 调整为东 9 区,然后重启 Java 应用。如下:
url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B9&useUnicode=true&characterEncoding=utf8
现在 serverTimezone 与数据库中不一致,数据库是东 8 区,serverTimezone 是东 9 区。
再次插入无时区的 8 点,如下:
然后再查询一把,结果如下:
time_stamp 字段值还是 8 点,数据库是东 8 区,所以这是东 8 区的 8 点。我们 serverTimezone 与数据库的时区不一致啊,但却没看到时间有偏差,这又是为什么?
解释一下
前面说过了,对于 JDBC 中的 Date 对象,在发送给 MySQL 前,会先根据 serverTimezone 转换为相应时区的时间字符串,但现在 Entity 中时间属性是 String 类型,MySQL 驱动不会进行转换,所以不管 serverTimezone 怎么配置,对 String 类型的时间串都没影响。
这样的话,似乎 Java 中日期类型用时间字符串来存还好些,不容易出错。但请再认真考虑一下,调用方传了一个无时区的 8 点,数据库自作主张,就将其认为是东 9 区的 8 点,但如果这个时间字符串实际是东 8 区的 8 点呢?这时如果保存到数据库中为东9区的8点,那数据就存错了。
如果目前 API 接口就传的是无时区的时间串,Entity 中就定义的 String,怎么解决呢?
-
询问接口定义人员,这个接口的时间字符串指的是哪个时区的?比如是东 8 区的 2020-02-23 08:00:00;
-
然后接口接收到时间后,要以东 8 区将时间字符串转换为 Date 对象,如下:
SimpleDateFormat sdf = new SimpleDateFormat('yyyy-MM-dd HH:mm:ss');
sdf.setTimeZone(TimeZone.getTimeZone("GMT+8"));
Date date = sdf.parse("2020-02-23 08:00:00"); -
如果 Entity 中时间属性定义的是 String,那么我们要再将 Date 对象以数据库的时区格式化为对应的时间字符串。比如,数据库时区是东 9 区,那么格式化后就是 2020-02-23 09:00:00,如下:
SimpleDateFormat sdf = new SimpleDateFormat('yyyy-MM-dd HH:mm:ss');
sdf.setTimeZone(TimeZone.getTimeZone("GMT+9"));
String dateStr = sdf.format(date);
entity.setTimeStamp(dateStr); -
最后,将 Entity 保存到 MySQL 中的,就也会是东 9 区的 2020-02-23 09:00:00,结果正确。
所以,使用 String 类型来存储时间数据,要想将时间值保存正确超级麻烦,不建议在实际开发中这样使用。
最佳实践
1. 大多数团队会规定 API 中传递时间要用 UNIX 时间缀。
因为如果你传一个 2020-02-23 08:00:00 时间值,它到底是哪个时区的 8 点呢?对于 UNIX 时间戳,就不会有此问题,因为它是绝对时间。而如果某些特殊原因,一定要使用时间字符串,最好使用 ISO8601 规范那种带时区的时间串,比如 2020-02-23T08:00:00+08:00。
2. Mybatis 中 Entity 定义要与数据库定义一致。
数据库中是 timestamp,那么 Entity 中要定义为 Date 对象,因为 MySQL 驱动在执行 SQL 时,会自动根据 serverTimezone 配置帮你转换为数据库时区的时间串。如果你自己来转换,你极有可能因为忘记调用 setTimeZone() 方法,而使用当前 Java 应用所在机器的默认时区,一旦 Java 应用所在机器的时区与数据库的时区不一致,就会出现时区问题。
3. JDBC 的 serverTimezone 参数要配置正确。
当不配置时,MySQL 驱动会自动读取 MySQL Server 的时区,此时一定要将 MySQL Server 的时区指定为清晰的时区(如 +08:00),切勿使用 CST。
4. 如果数据库时区修改后,JDBC 的 serverTimezone 也要跟着修改,并重启 Java 应用。
就算没有配置 serverTimezone,也需要重启。因为 MySQL 驱动初始化连接时,会将当前数据库时区缓存到一个 Java 变量中,不重启 Java 应用它不会变。
数据库中用 timestamp 还是 int 来存储时间?
如果用 int 型时间戳存储,不管数据库时区是啥都不影响,因为存储的是绝对时间。看起来完美解决了时区问题。
但从某些角度看,这种方案只是把时区问题从数据库端推到应用端去了,时区问题将出现在将时间字符串转换为时间缀的过程中。比如某程序员从 API 接口中拿到时间字符串后,没考虑时区,直接转为 UNIX 时间缀,就可能出现时区问题。
因此,对于不带时区的时间串解析,一定要问清楚这是哪个时区的时间,并在代码中显式指定。
另外,用 int 存储时间还有如下 3 个不好的点:
-
开发人员看到这个字段后,无法一目了然的了解到这个时间缀大概是个什么时间,需要去转换一下,会很繁琐;
-
像 update_time 这样的字段,数据库提供了DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 的机制,这样在更新任何字段时,update_time 会自动更新。而如果使用 int存储,就需要程序员每次更新表时,重新 set 这个字段,容易遗忘;
-
由于 int 只有 4 个字节,用它来存储时间,会在 2038 年后溢出,而对于 timestamp来说,MySQL 将其底层存储统一修改为 8 个字节,相对来说还是比较容易的。
当然,也并不是建议不用 int,这是见仁见智的。不管用 timestamp 还是 int,都没有致命性问题。
总结
timestamp 本身是没有时区问题的,时区问题是由于 serverTimezone 配置错误、MySQL 使用 CST 这种混乱时区或 Entity 中将日期定义 String 类型导致的。
学习更多JAVA知识与技巧,关注与私信博主(555)!
热爱学习和渴望进阶的小伙伴,各种JAVA学习路线、笔记、面试题,免费分享!