【Mysql】The DATE, DATETIME, and TIMESTAMP Types（一）https://developer.aliyun.com/article/1395349
CST/UTC/GMT 是什么？
CST：中国标准时间（China Standard Time），这个解释可能是针对RedHat Linux。
UTC：协调世界时，又称世界标准时间，简称UTC，从英文国际时间/法文协调时间”Universal Time/Temps Cordonné”而来。中国大陆、香港、澳门、台湾、蒙古国、新加坡、马来西亚、菲律宾、澳洲西部的时间与UTC的时差均为+8，也就是UTC+8。
GMT：格林尼治标准时间（旧译格林威治平均时间或格林威治标准时间；英语：Greenwich Mean Time，GMT）是指位于英国伦敦郊区的皇家格林尼治天文台的标准时间，因为本初子午线被定义在通过那里的经线。
个人验证1：timestamp 是如何工作的
注意下面的所有实验均在控制台进行，请不要使用Navicat进行测试，看到的结果和控制台结果存在差异。
MySQL converts
TIMESTAMPvalues from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.
MySQL 将 TIMESTAMP 值从当前时区转换到 UTC 以进行存储，并从 UTC 返回到当前时区以进行检索。
在Mysql中可以通过下的语句查看当前的时区信息：
SHOW VARIABLES LIKE '%time_zone%'
这里解释下UTC以及SYSTEM的含义：
system_time_zone：表明使用系统时间。time_zone：相对于 UTC 时间的偏移，比如 '+08:00' 或者 '-6:00'。
全局参数 system_time_zone
系统时区，在MySQL启动时，会检查当前系统的时区，根据系统时区设置全局参数system_time_zone的值。
The system time zone. When the server starts, it attempts to determine the time zone of the host machine automatically and uses it to set the
system_time_zonesystem variable. The value does not change thereafter.系统时区。服务器启动时，会尝试自动确定主机的时区，并以此设置
system_time_zone系统变量。此后，该值不会改变。
注意，system_time_zone 变量只有全局值没有会话值，不能动态修改，MySQL 启动时，将尝试自动确定服务器的时区，并使用它来设置 system_time_zone 系统变量。
全局参数 time_zone
用来设置每个连接会话的时区，默认为system时，使用全局参数system_time_zone的值。
The current time zone. This variable is used to initialize the time zone for each client that connects. By default, the initial value of this is 'SYSTEM' (which means, “use the value of system_time_zone”).
当前时区。该变量用于为每个连接的客户端初始化时区。默认情况下，初始值为 "SYSTEM"（即 "使用 system_time_zone 的值"）。
需要注意，在一些系统中，system_time_zone的值是CST，中国标准时间=CST(China Standard Time) UT+8:00 ，mysql的时区=system_time_zone+time_zone。
下面是在Session当中通过更改时区对应timestamp的影响。
-- 时间戳测试表和数据 CREATE TABLE `timestamp_test` ( `id` varchar(50) NOT NULL COMMENT '主键', `time` timestamp NULL COMMENT '时间戳', PRIMARY KEY (`id`) ); -- 存入 + 8 时区 INSERT INTO `timestamp_test` (`id`, `time`) VALUES ('1', '2023-10-17 13:48:55'); INSERT INTO `timestamp_test` (`id`, `time`) VALUES ('2', '2023-10-17 13:10:55'); INSERT INTO `timestamp_test` (`id`, `time`) VALUES ('3', '2023-10-17 13:22:55'); -- 查看时区 show variables like '%time_zone%'; -- system_time_zone UTC -- time_zone SYSTEM -- 更改时区 set session time_zone = '+8:00'; -- 查看时间 select id,`time` from timestamp_test; -- 1 2023-10-17 13:48:55 -- 2 2023-10-17 13:10:55 -- 3 2023-10-17 13:22:55 set session time_zone = '+2:00'; -- 查看时间 select id,`time` from timestamp_test; -- +----+---------------------+ -- | id | time | -- +----+---------------------+ -- | 1 | 2023-10-17 15:48:55 | -- | 2 | 2023-10-17 15:10:55 | -- | 3 | 2023-10-17 15:22:55 | -- +----+---------------------+
在当前session修改时区之后，对应的timestamp读取时间也出现变化。
个人验证2：时区设置影响
参考：opensource.actionsky.com/20211214-ti…
1.NOW() 和 CURTIME() 系统函数的返回值受当前 session 的时区影响
select now()，包括insert .. values(now())、以及字段的 DEFAULT CURRENT_TIMESTAMP 属性也受此影响。这里依旧使用上面的案例：
-- 时间戳测试表和数据 CREATE TABLE `timestamp_test` ( `id` varchar(50) NOT NULL COMMENT '主键', `time` timestamp NULL COMMENT '时间戳', PRIMARY KEY (`id`) ); -- 存入 + 8 时区 INSERT INTO `timestamp_test` (`id`, `time`) VALUES ('1', '2023-10-17 13:48:55'); INSERT INTO `timestamp_test` (`id`, `time`) VALUES ('2', '2023-10-17 13:10:55'); INSERT INTO `timestamp_test` (`id`, `time`) VALUES ('3', '2023-10-17 13:22:55');
更改数据库时区为+02:00之后，对应结果如下：
mysql> show variables like '%time_zone%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | UTC | | time_zone | +02:00 | +------------------+--------+ 2 rows in set (0.01 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2023-07-21 21:51:48 | +---------------------+ 1 row in set (0.00 sec)
可以看到，修改time_zone会对函数的结果产生影响。
2.timestamp 数据类型字段存储的数据受时区影响
根据Mysql文档的描述，timestamp 数据类型会存储当前session的时区信息，读取时会根据当前 session 的时区进行转换，而date和datetime则不会因为时区的变更而出现数据变更。
国内安装Mysql的时区问题避坑
1.明确指定时区
在 my.cnf 写入 default-time-zone='+08:00'，其他地区和开发确认取对应时区即可。
至于为什么要明确指明时区，一方面是Mysql 在很多没有DBA的公司都是全部由运维负责，运维如果没有设置时区，在数据库迁移到海外服务器的时候可能会出现时区变更的各种问题，另一方面是这样明确的设置可以减少系统计算的开销，如果系统大量使用timestamp的数据类型，这也是一个不小的优化点。
2.JAVA应用读取到的时间和北京时间差了14个小时，为什么？怎么解决？
基于mysql-connector-java-8.0.19 进行分析，使用 com.mysql.cj.jdbc.Driver，在获取java.sql.Connection的过程会确定时区，调用如下方法。
com.mysql.cj.protocol.a.NativeProtocol#configureTimezone /** * Configures the client's timezone if required. * * @throws CJException * if the timezone the server is configured to use can't be * mapped to a Java timezone. */ public void configureTimezone() { String configuredTimeZoneOnServer = this.serverSession.getServerVariable("time_zone"); if ("SYSTEM".equalsIgnoreCase(configuredTimeZoneOnServer)) { configuredTimeZoneOnServer = this.serverSession.getServerVariable("system_time_zone"); } String canonicalTimezone = getPropertySet().getStringProperty(PropertyKey.serverTimezone).getValue(); if (configuredTimeZoneOnServer != null) { // user can override this with driver properties, so don't detect if that's the case if (canonicalTimezone == null || StringUtils.isEmptyOrWhitespaceOnly(canonicalTimezone)) { try { canonicalTimezone = TimeUtil.getCanonicalTimezone(configuredTimeZoneOnServer, getExceptionInterceptor()); } catch (IllegalArgumentException iae) { throw ExceptionFactory.createException(WrongArgumentException.class, iae.getMessage(), getExceptionInterceptor()); } } } if (canonicalTimezone != null && canonicalTimezone.length() > 0) { this.serverSession.setServerTimeZone(TimeZone.getTimeZone(canonicalTimezone)); // // The Calendar class has the behavior of mapping unknown timezones to 'GMT' instead of throwing an exception, so we must check for this... // if (!canonicalTimezone.equalsIgnoreCase("GMT") && this.serverSession.getServerTimeZone().getID().equals("GMT")) { throw ExceptionFactory.createException(WrongArgumentException.class, Messages.getString("Connection.9", new Object[] { canonicalTimezone }), getExceptionInterceptor()); } } this.serverSession.setDefaultTimeZone(this.serverSession.getServerTimeZone()); }
上面的代码大致逻辑如下：
- 从mysql的
time_zone读取值。 - 如果值是
SYSTEM则使用system_time_zone的值. - 如果
jdbc url配置了时区则使用url里的，如jdbc:mysql://localhost:3306/test?useSSL=true&serverTimezone=Asia/Shanghai，则最优先使用URL设置的时区。
那么为什么JAVA应用读取到的时间和北京时间差了14个小时？通常是因为没有在URL里面设置时区属性，某些系统下，MySQL默认使用的是系统时区CST（CST 在 RedHat 上是 +08:00 时区），而应用和MySQL 建立的连接的session time_zone为CST。
实际上，CST 一共能代表4个时区：
Central Standard Time (USA) UT-6:00美国标准时间Central Standard Time (Australia) UT+9:30澳大利亚标准时间China Standard Time UT+8:00中国标准时间Cuba Standard Time UT-4:00古巴标准时间
虽然Mysql正确认识了CST是中国标准时间，但是JDBC却没有认识这个时间，JDBC在解析CST时使用了美国标准时间，这就会导致时区错误。
具体可以看下面的代码：
public class TimeTest { public static void main(String[] args) { // 这里的CST指的是美国中部时间， TimeZone tz = TimeZone.getTimeZone("CST"); System.out.println("tz => "+ tz);// 可以看到偏移量是offset=-21600000，-21600000微秒=-6小时，所以这里的CST指美国 // 建议创建 TimeZone 用 ZoneId，因为ZoneId 不允许 CST、JST 这种简称，能提前预防进坑，如下 // ZoneId zoneId = ZoneId.of("CST");// 抛异常 ZoneId zoneId = ZoneId.of("GMT+8");// 明确指定，是OK的，或者 "区域/城市" 的写法如 Asia/Shanghai TimeZone tz1 = TimeZone.getTimeZone(zoneId); System.out.println("tz1 => "+ tz1); }/**运行结果： tz => sun.util.calendar.ZoneInfo[id="CST",offset=-21600000,dstSavings=3600000,useDaylight=true,transitions=235,lastRule=java.util.SimpleTimeZone[id=CST,offset=-21600000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=2,startDay=8,startDayOfWeek=1,startTime=7200000,startTimeMode=0,endMode=3,endMonth=10,endDay=1,endDayOfWeek=1,endTime=7200000,endTimeMode=0]] tz1 => sun.util.calendar.ZoneInfo[id="GMT+08:00",offset=28800000,dstSavings=0,useDaylight=false,transitions=0,lastRule=null] */}
3.修改MySQL的时区会影响已经存储的时间类型数据吗？
答案是只会影响对 timestamp 数据类型的读取。
4.迁移数据时会有导致时间类型数据时区错误的可能吗？
这一点依然是针对 timestamp 数据类型，比如使用 mysqldump 导出 csv 格式的数据，默认这种导出方式会使用 UTC 时区读取 timestamp 类型数据，这意味导入时必须手工设置 session.time_zone=’+00:00’才能保证时间准确。
--将 test.t 导出成 csv mysqldump -S /data/mysql/data/3306/mysqld.sock --single-transaction \ --master-data=2 -t -T /data/backup/test3 --fields-terminated-by=',' test t --查看导出数据 cat /data/backup/test3/t.txt 2021-12-02 08:45:39,2021-12-02 16:45:39
为了避免这些繁琐的操作，mysqldump 也提供了一个参数 --skip-tz-utc，意思就是导出数据的那个连接不设置 UTC 时区，使用 MySQL 的 gloobal time_zone 系统变量值。
当然这个设置也算是告诉我们，mysqldump 导出默认也是使用 UTC 时区，为了确保导出和导入的时区正确，会在导出的 sql 文件头部带有 session time_zone 信息。
需要注意
--compact参数会去掉 sql 文件的所有头信息，所以--compact参数得和--skip-tz-utc一起使用。
为什么mysql有system_time_zone和time_zone两个?
- Mysql 服务的时候会读取Linux宿主机所在的时区，固定值之后这个值不再改变。（简单说
system_time_zone是Mysql系统算出来的值）。 - 默认情况
time_zone值为 SYSTEM，也就是说它跟随system_time_zone的值。 - 注意
system_time_zone的值固定下来后，数据库宿主机的时区再改变，time_zone的值都是不变的，因为它是跟随system_time_zone变量的，不是实时跟随操作系统的，如果想要让他跟随操作系统，最简单的方法就是重启Mysql。 - 有时候我们会发现，Linux时区是对的，但是mysql的时区是错，这时候我们把Linux的时区改对，但是发现Mysql还是错的，原因是Linux时区改对之后没有重启Mysql服务器重新读取Linux系统时区。
