数据库时区那些事儿 - MySQL的时区处理

Ku_Andrew 发布于2019-08-01 13:43 / 1064人阅读

摘要：本文探究了及其驱动对于时区的处理方式，并尝试给出最佳实践。只要保证时区和用户所在时区保持一致即可。下面是运行结果中国标准时间中欧时间可以看到结果是基本符合文档里的说明的，但是要注意，在时区，和的时间部分相差一小时。

原文地址

当JVM时区和数据库时区不一致的时候，会发生什么？这个问题也许你从来没有注意过，但是当把Java程序容器化的时候，问题就浮现出来了，因为目前几乎所有的Docker Image的时区都是UTC。本文探究了MySQL及其JDBC驱动对于时区的处理方式，并尝试给出最佳实践。

先给总结

DATE和TIME类型不支持时区转换。

对于TIMESTAMP类型，MySQL会正确的根据connection时区（对于JDBC来说就是JVM时区）/服务端时区做转换。

JDBC程序不需要特别注意什么事情。只要保证JVM时区和用户所在时区保持一致即可。

不要在服务器端做日期时间的字符串格式化（DATE_FORMAT()），因为返回的结果是服务端的时区，而不是connection的时区（对于JDBC来说就是JVM时区）。

CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE()可以安全的使用，返回的结果会转换成connection时区（对于JDBC来说就是JVM时区）。

CURRENT_TIME()有一个不知道是不是BUG的Bug #92453。

日期时间类型的时区

MySQL - The DATE, DATETIME, and TIMESTAMP Types：

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the 
current time zone for retrieval. (This does not occur for other types such as DATETIME.) 
By default, the current time zone for each connection is the server"s time. The time zone can be set on 
a per-connection basis. 
As long as the time zone setting remains constant, you get back the same value you store. 
If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value 
is different from the value you stored. This occurs because the same time zone was not used for conversion 
in both directions.

简而言之就是两句话：

查询TIMESTAMP类型所返回的值，会根据connection的时区（对于JDBC来说就是JVM时区）做转换

在MySQL中只有TIMESTAMP类型会做时区转换

为了验证这个结论，我写了一段程序来实验，这个程序做了三件事情：

使用Asia/Shanghai时区构造一个日期java.util.Date：2018-09-14 10:00:00，然后插入到数据库里（表：test，列：timestamp类型）

使用Asia/Shanghai时区把这个值再查出来，看看结果。

使用Asia/Shanghai时区，获得这个字段的格式化字符串（使用DATE_FORMAT()函数）。

使用Europe/Paris时区重复第2-3步的动作

在运行程序之前，我们先用Docker启动一个MySQL，它所在的MySQL的时区是UTC（除非特别设定，所有Docker Image时区都默认为UTC）：

docker run --name mysql-timezone-test 
  -e MYSQL_RANDOM_ROOT_PASSWORD=yes 
  -e MYSQL_DATABASE=testdb 
  -e MYSQL_USER=tz 
  -e MYSQL_PASSWORD=tz 
  -p 3306:3306 
  -d mysql:8

下面是结果：

Insert data, Time Zone        : 中国标准时间
java.util.Date                : 2018-09-14 10:00:00
Insert into timestamp column  : 2018-09-14 10:00:00
--------------------
Retrieve data, Time Zone      : 中国标准时间
Retrieve java.util.Date       : 2018-09-14 10:00:00
Retrieve formatted string     : 2018-09-14 02:00:00
--------------------
Retrieve data, Time Zone      : 中欧时间
Retrieve java.util.Date       : 2018-09-14 04:00:00
Retrieve formatted string     : 2018-09-14 02:00:00

可以看到Retrieve java.util.Date返回的结果根据JVM时区做了转换的。而Retrieve formatted string返回的结果则是UTC时间。

当前日期时间相关函数

MySQL与"当前日期时间"相关的函数有这么些，MySQL - Date and Time Functions：

The CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE(), and FROM_UNIXTIME() functions return values 
in the connection"s current time zone, which is available as the value of the time_zone system variable.

而且根据文档所讲，它们返回的结果匹配当前连接所设定的时区。

为了验证这个结论，同样写了一段程序，分别使用Asia/Shanghai和Europe/Paris来调用CURRENT_TIMESTAMP()、CURRENT_TIME()、CURRENT_DATE()。

下面是运行结果：

JVM Time Zone              : 中国标准时间
Test CURRENT_DATE()        : 2018-09-18
Test CURRENT_TIME()        : 10:55:41
Test CURRENT_TIMESTAMP()   : 2018-09-18 10:55:41.0
--------------------
JVM Time Zone              : 中欧时间
Test CURRENT_DATE()        : 2018-09-18
Test CURRENT_TIME()        : 03:56:02
Test CURRENT_TIMESTAMP()   : 2018-09-18 04:56:02.0

可以看到结果是基本符合文档里的说明的，但是要注意，在Europe/Paris时区，CURRENT_TIME()和CURRENT_TIMESTAMP()的时间部分相差一小时。
看上去CURRENT_TIMESTAMP()返回的是UTC DST offset结果，而CURRENT_TIME()返回的是UTC offset结果，关于这个我登记了Bug #92453。
关于Europe/Paris的DST信息可以在这里找到Wiki - List of tz database time zones。

在MySQL客户端操作时区

-- 查询系统时区和session时区
SELECT @@global.time_zone, @@session.time_zone;

-- 设置session时区
SET time_zone = "Asia/Shanghai";

详见：MySQL Server Time Zone Support

Docker启动时设定时区

~~你可以在docker启动的时候设定MySQL容器的时区，比如这样-e TZ=Asia/Shanghai。~~

这个方法有问题，会出现时间错乱，workaround是root用户连接到MySQL，然后执行SET GLOBAL time_zone = "Asia/Shanghai";。

这样客户端连接MySQL时，查询的时间的时区都是Asia/Shanghai了。

参考资料

MySQL - The DATE, DATETIME, and TIMESTAMP Types

MySQL - Date and Time Functions

MySQL Server Time Zone Support

Wiki - List of tz database time zones

W3C- Working with timezone

相关代码

https://github.com/chanjarste...

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/48851.html

数据库时区那些事儿 - Oracle的时区处理

摘要：本文探究了及其驱动对于时区的处理方式，并尝试给出最佳实践。和类似，不过存储的数据会标准化为数据库的时区，用户获取它的时候会转换成用户时区对于来说，就是时区。而和返回的则是数据库所在操作系统所在时区的时间。原文地址当JVM时区和数据库时区不一致的时候，会发生什么？这个问题也许你从来没有注意过，但是当把Java程序容器化的时候，问题就浮现出来了，因为目前几乎所有的Docker Imag...

Chaz 2019-07-23 17:49 评论0 收藏0
数据库时区那些事儿 - Oracle的时区处理

摘要：本文探究了及其驱动对于时区的处理方式，并尝试给出最佳实践。和类似，不过存储的数据会标准化为数据库的时区，用户获取它的时候会转换成用户时区对于来说，就是时区。而和返回的则是数据库所在操作系统所在时区的时间。原文地址当JVM时区和数据库时区不一致的时候，会发生什么？这个问题也许你从来没有注意过，但是当把Java程序容器化的时候，问题就浮现出来了，因为目前几乎所有的Docker Imag...

snowLu 2019-07-23 17:00 评论0 收藏0
Java程序制作Docker Image推荐方案

摘要：如果你用的是新建用户命令是这样的如果你用的是或者新建用户命令是这样的然后使用指令后面的指令就都是以用户身份执行了指定程序的接口对于联网应用而言，必须在中指定暴露的端口，否则该端口无法映射。详见参考文档命令教程原文地址本文源代码是一个spring-boot应用（在 https://github.com/chanjarste... ），不过本例子适用于所有Java应用。要求这里先给...

xuhong 2019-08-19 11:12 评论0 收藏0
Java程序制作Docker Image推荐方案

摘要：如果你用的是新建用户命令是这样的如果你用的是或者新建用户命令是这样的然后使用指令后面的指令就都是以用户身份执行了指定程序的接口对于联网应用而言，必须在中指定暴露的端口，否则该端口无法映射。详见参考文档命令教程原文地址本文源代码是一个spring-boot应用（在 https://github.com/chanjarste... ），不过本例子适用于所有Java应用。要求这里先给...

dayday_up 2019-06-28 16:39 评论0 收藏0
数据库存储时间的时区问题

摘要：用这个类型还蛮方便的，一个是有很多内置的函数和来处理它，比如宏，最关键的是在取数据的时候会自动帮你处理和时区的问题。至于用存时间，是另一种可行的方法，参见我个人不是很喜欢这么做，因为这样你必须把模型中表示时间的成员声明为类型。先说一下mysql中DATETIME和TIMESTAMP的区别 TIMESTAMP是标准的unix timestamp，它存储的是1970-1-1到现在经过的...

gitmilk 2019-08-06 13:50 评论0 收藏0