devbox
花生_TL007
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

彻底搞懂MySQL TimeStamp的时区问题_mysql时区

作者：花生_TL007 | 2024-05-24 11:58:15

赞

踩

mysql时区

彻底搞懂MySQL TimeStamp的时区问题

mysql中有两个时间类型，timestamp与datetime，其中timestamp在存储上是包含时区的，而datetime是不包含时区的字符串形式。而通常应用下所说的时区问题，也指的是Java应用使用了jdbc驱动时，存储和读取的时区不一致的问题，两者可能会相差8小时或者13小时，今天，就来彻底搞懂为什么会发生这种所谓的时区问题。

首先需要明白，JDK以版本8为界，有两套处理日期/时间的API：Date和JSR 310，其中，Date对象是绝对时间，Date对象里存的是自格林威治时间（GMT）1970年1月1日0点至Date所表示时刻所经过的毫秒数。

Date类通过SimpleDateFormat格式化出来的yyyy-MM-dd HH:mm:ss形式的时间字符串，是带了时区的本地时间，如果SimpleDateFormat没有调用setTimeZone()显示指定时区，那么默认用的是jvm运行在的操作系统上的时区，我们开发机上的时区基本都是GMT+8。

我们首先需要搞明白上面的众多名词：GMT，UTC，时间标准，时区，以及与时区相关的夏令时的概念，才会对本次要讲的内容做很好的理解。

时间标准

GMT：Greenwich Mean Time

格林威治标准时间 ; 格林威治皇家天文台为了海上霸权的扩张计划，在十七世纪就开始进行天体观测。为了天文观测，选择了穿过英国伦敦格林威治天文台子午仪中心的一条经线作为零度参考线，这条线，简称格林威治子午线。

英国伦敦格林威治定为0°经线开始的地方，地球每15°经度 被分为一个时区，共分为24个时区，GMT、UTC、DST、CST相邻时区相差一小时；例例：中国北京位于东八区，GMT时间比北京时间慢8小时。

1972年之前，格林威治时间（GMT）一直是世界时间的标准。1972年之后，GMT 不再是一个时间标准了。

UTC：世界协调时间

UTC 是现在全球通用的时间标准，全球各地都同意将各自的时间进行同步协调。UTC 时间是经过平均太阳时（以格林威治时间GMT为准）、地轴运动修正后的新时标以及以秒为单位的国际原子时所综合精算而成。

其以原子时秒长为基础，精确到秒，误差在0.9s以内， 是比GMT更为精确的世界时间。需要注意的是：协调世界时不与任何地区位置相关，也不代表此刻某地的时间，所以在说明某地时间时要加上时区，也就是说GMT并不等于UTC，而是等于UTC+0，只是格林尼治刚好在0时区上。GMT实际相当于UTC+0，格林威治在0时区上。例如：中国标准时间比协调世界时早 8 小时，记为：UTC+8

时区

从格林威治本初子午线起，经度每向东或者向西间隔15°，就划分一个时区，在这个区域内，大家使用同样的标准时间。

但实际上，为了照顾到行政上的方便，常将1个国家或1个省份划在一起。所以时区并不严格按南北直线来划分，而是按自然条件来划分。另外：由于目前，国际上并没有一个批准各国更改时区的机构。一些国家会由于特定原因改变自己的时区。

各个时区的标准时间都有自己的简写，例如美国东部标准时间叫：EST，Estern Standard Time，但是有的简写会重复，这就是MySQL时区为什么会“错乱”的原因之一，后续会讲

但是CST同时是四个不同时区的缩写，存在歧义

  1. Central Standard Time (USA) UT-6:00 美国标准时间
  2. Central Standard Time (Australia) UT+9:30 澳大利亚标准时间
  3. China Standard Time UT+8:00 中国标准时间
  4. Cuba Standard Time UT-4:00 古巴标准时间

需要注意的是，Java语言中的时间戳表示从1970年1月1日 00:00:00到现在所经历的秒数，与时区无关

夏令时

时区之外，还需要了解夏令时的概念，如今中国已不再使用夏令时，但是仍然有一些国家在使用夏令时，夏令时（DST: Daylight Saving Time），夏季节约时间，即夏令时；是为了利用夏天充足的光照而将时间调早一个小时，北美、欧洲的许多国家实行夏令时；

Java的Date类

可以看到，在时区设置不为空的情况下，toString方法可以以ISO时间标准表示方法输出JDK所在地区的GMT标准时间

    public String toString() {
        // "EEE MMM dd HH:mm:ss zzz yyyy";
        BaseCalendar.Date date = normalize();
        StringBuilder sb = new StringBuilder(28);
        int index = date.getDayOfWeek();
        if (index == BaseCalendar.SUNDAY) {
            index = 8;
        }
        convertToAbbr(sb, wtb[index]).append(' ');                        // EEE
        convertToAbbr(sb, wtb[date.getMonth() - 1 + 2 + 7]).append(' ');  // MMM
        CalendarUtils.sprintf0d(sb, date.getDayOfMonth(), 2).append(' '); // dd

        CalendarUtils.sprintf0d(sb, date.getHours(), 2).append(':');   // HH
        CalendarUtils.sprintf0d(sb, date.getMinutes(), 2).append(':'); // mm
        CalendarUtils.sprintf0d(sb, date.getSeconds(), 2).append(' '); // ss
        TimeZone zi = date.getZone();
        if (zi != null) {
            sb.append(zi.getDisplayName(date.isDaylightTime(), TimeZone.SHORT, Locale.US)); // zzz
        } else {
            sb.append("GMT");
        }
        sb.append(' ').append(date.getYear());  // yyyy
        return sb.toString();
    }

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24

getTime()方法可以获取毫秒数，返回值类型是long

new Date().getTime();

  • 1

示例

Date date=new Date();
System.out.println(date.toString());
System.out.println(date.toLocaleString());//弃用
System.out.println(date.toGMTString());//弃用
//Thu Jul 14 23:55:10 CST 2022
//2022-7-14 23:55:10
//14 Jul 2022 15:55:10 GMT

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

JDK8之前，Java对时区和偏移量都使用TimeZone类来表示的。Java不可以使用UTC+8。

getDefault()方法

sun.util.calendar.ZoneInfo[id="Asia/Shanghai",offset=28800000,dstSavings=0,useDaylight=false,transitions=31,lastRule=null]

  • 1

时区转换

可以使用SimpleDateFormat对时间在不同时区间进行转换

//Java 中的 UTC和 GMT
jshell> Date now = new Date()
now ==> Thu Oct 30 16:59:27 CST 2021

jshell> import java.text.*

jshell> DateFormat df = DateFormat.getInstance()
df ==> java.text.SimpleDateFormat@ca92313f

jshell> df.setTimeZone(TimeZone.getTimeZone("GMT+8"))

jshell> df.format(now)
$5 ==> "2021/10/30 下午4:59

jshell> df.setTimeZone(TimeZone.getTimeZone("UTC+8"))

jshell> df.format(now)
$7 ==> "2021/10/30 上午8:59"

jshell> df.setTimeZone(TimeZone.getTimeZone("UTC"))

jshell> df.format(now)
$9 ==> "2021/10/30 上午8:59"

jshell> df.setTimeZone(TimeZone.getTimeZone("GMT"))

jshell> df.format(now)
$11 ==> "2021/10/30 上午8:59"

jshell> df.setTimeZone(TimeZone.getTimeZone("abc"))

jshell> df.format(now)
$13 ==> "2021/10/30 上午8:59"

//从上面的输出可以看出，在当前使用的 Java 版本中，设定时区偏移量时，是不能使用 UTC的，只能使用 GMT！另外，使用非法的时区 ID 时，会将时区设定为零时区。

//GMT+8和 Asia/Shanghai
//通常我们会将日期表示成格式化的字符串，如 2021-10-31 10:34:00，然后将其解析为日期类型并使用，需要打印时再将日期类型转换为字符串，例如：

jshell> import java.text.*

jshell> SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
sdf ==> java.text.SimpleDateFormat@4f76f1a0

jshell> DateFormat df = DateFormat.getInstance()
df ==> java.text.SimpleDateFormat@ca92313f

jshell> df.setTimeZone(TimeZone.getTimeZone("Asia/Shanghai"))

jshell> df.format(sdf.parse("2021-10-31 10:34:00"))
$6 ==> "2021/10/31 上午10:34"

jshell> df.setTimeZone(TimeZone.getTimeZone("GMT+8"))

jshell> df.format(sdf.parse("2021-10-31 10:34:00"))
$8 ==> "2021/10/31 上午10:34"

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56

Java中的夏令时问题

前面谈到了夏令时问题，所以会有这样的疑问：GMT+8东八区时间一定是地区时间Asia/Shanghai吗？下面是夏令时问题。

jshell> df.setTimeZone(TimeZone.getTimeZone("Asia/Shanghai"))

jshell> df.format(sdf.parse("1986-05-04 02:00:00"))
$10 ==> "1986/5/4 上午3:00"

jshell> df.setTimeZone(TimeZone.getTimeZone("GMT+8"))

jshell> df.format(sdf.parse("1986-05-04 02:00:00"))
$12 ==> "1986/5/4 上午2:00"

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

当我们使用1986-05-04 02:00:00这个时间戳时：

时区设置为 GMT+8，字符串转日期，再将日期转成字符串，得到的日期和时间跟输入的时间戳是一样的，时区设置为 Asia/Shanghai，经过转换之后，输出的时间变成了上午三点，比输入的上午两点多了一小时，这是因为夏令时造成的。

由此可以看到，仅仅使用偏移量无法准确的描述一个地区的时间，因此Java推出了JSR 310使用时区来描述当地的时间。

TIPS: GMT-8和Asia/Shanghai

  • GMT-8是东八区，北京时间和东八区一致。
  • Asia/Shanghai是已地区命名的地区标准时，这个地区标准时会兼容历史各个时间节点。中国1986-1991年实行夏令时，夏天和冬天差1个小时，Asia/Shanghai会兼容这个时间段。1992年以后，在中国，GMT-8和Asia/Shanghai是一样的时间，1986-1991之间，夏天会有一小时时差。

JSR310

时区和偏移量在概念和实际作用上是有较大区别的，主要体现在：UTC偏移量仅仅记录了偏移的小时分钟而已，除此之外无地理/时区含义。比如：+08:00的意思是比UTC时间早8小时，没有地理/时区含义，相应的-03:30代表的意思仅仅是比UTC时间晚3个半小时。

时区是特定于地区而言的，它和地理上的地区（包括规则）强绑定在一起。比如整个中国都叫东八区，纽约在西五区等等。

中国没有夏令时，所有东八区对应的偏移量永远是+8；纽约有夏令时，因此它的偏移量可能是-4也可能是-5，因此规定了时区后，我们可以根据夏令时准确的计算当地时间

ZoneId

在JDK 8之前，Java使用java.util.TimeZone来表示时区。而在JDK 8里分别使用了ZoneId表示时区，ZoneOffset表示UTC的偏移量。

ZoneId在系统内是唯一的，它共包含三种类型的ID：

  • 最简单的ID类型：ZoneOffset，它由’Z’和以’+‘或’-'开头的id组成。如：Z、+18:00、-18:00

  • 另一种类型的ID是带有某种前缀形式的偏移样式ID，例如’GMT+2’或’UTC+01:00’。可识别的（合法的）前缀是’UTC’， ‘GMT’和’UT’

  • 第三种类型是基于区域的ID（推荐使用）。基于区域的ID必须包含两个或多个字符，且不能以’UTC’、‘GMT’、‘UT’ '+‘或’-'开头。基于区域的id由配置定义好的，如Europe/Paris

System.out.println(ZoneId.systemDefault());//Asia/Shanghai

  • 1

ZoneId底层使用的也是TimeZone

    /**
     * Gets the system default time-zone.
     * <p>
     * This queries {@link TimeZone#getDefault()} to find the default time-zone
     * and converts it to a {@code ZoneId}. If the system default time-zone is changed,
     * then the result of this method will also change.
     *
     * @return the zone ID, not null
     * @throws DateTimeException if the converted zone ID has an invalid format
     * @throws ZoneRulesException if the converted zone region ID cannot be found
     */
    public static ZoneId systemDefault() {
        return TimeZone.getDefault().toZoneId();
    }

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

ZoneOffset

时区偏移量是时区与格林威治/UTC之间的时间差。这通常是固定的小时数和分钟数。世界不同的地区有不同的时区偏移量。在ZoneId类中捕获关于偏移量如何随一年的地点和时间而变化的规则（主要是夏令时规则），所以继承自ZoneId。

JSR 310对日期、时间进行了分开表示（LocalDate、LocalTime、LocalDateTime），对本地时间和带时区的时间进行了分开管理。LocalXXX表示本地时间，也就是说是当前JVM所在时区的时间；ZonedXXX表示是一个带有时区的日期时间，它们能非常方便的互相完成转换。

// 本地日期/时间
System.out.println("================本地时间================");
System.out.println(LocalDate.now());
System.out.println(LocalTime.now());
System.out.println(LocalDateTimenow());

// 时区时间
System.out.println("================带时区的时间ZonedDateTime================");
System.out.println(ZonedDateTime.now()); // 使用系统时区
System.out.println(ZonedDateTime.now(ZoneId.of("America/New_York"))); // 自己指定时区
System.out.println(ZonedDateTime.now(Clock.systemUTC())); // 自己指定时区
System.out.println("================带时区的时间OffsetDateTime================");
System.out.println(OffsetDateTime.now()); // 使用系统时区
System.out.println(OffsetDateTime.now(ZoneId.of("America/New_York"))); // 自己指定时区
System.out.println(OffsetDateTime.now(Clock.systemUTC())); // 自己指定时区

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

MySQL中的时区

Java的时间实际上是经过jdbc转换为字符串的形式写入数据库，查询时同理以字符串的形式转换为Java Date类型，所以需要分析jdbc驱动，发现实际上在调用jdbc的setTimestamp()方法时，com.mysql.cj.jdbc.ClientPreparedStatement#setTimestamp()，这里面会根据serverTimezone指定的时区，将对应的Timestamp对象转换为serverTimezone指定时区的本地时间字符串。如果jdbc连接串的serverTimezone配置的时区和数据库配置的时区不一致时，查询的时间的时区以serverTimezone为准。

查看MySQL数据库当前时区：

SHOW variables LIKE '%time_zone%';


Variable_name   |Value |
----------------+------+
system_time_zone|CST   |
time_zone       |SYSTEM|

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

发现MySQL默认配置的是CST，然而对于CST，MySQL和Java却对其有不同的解读。

  • MySQL中，CST表示的是：中国标准时间(UTC+08:00)
  • Java中，CST表示的是：美国标准时间(UTC-05:00或UTC-06:00)

所以出现时区问题的根本原因是由于Java和MySQL服务端对CST时区的不同解读，最终导致了Java与MySQL时间不一致的问题。

比如一个时间值是东8区的2020-02-23 08:00:00，在Java代码中存储的是Date类型的绝对时间，由于serverTimeZone未配置，读取MySQL默认的time_zone为CST（中国标准时间），由于Java认为CST是美国标准时间的缩写，因此MySQL驱动会将这个Date对象转为字符串类型的2020-02-22 19:00:00，将sql发送给MySQL，然后MySQL数据库接收到这个时间字符串2020-02-22 19:00:00后，由于数据库时区配置是CST中国标准时间东8区，数据库会认为是东8区的2020-02-22 19:00:00。查询时MySQL返回了东8区的时间字符串2020-02-22 19:00:00，Java拿到后认为是CST美国标准时间的2020-02-22 19:00:00，最后以UTC+8标准时间输出到控制台，就显示为2020-02-23 08:00:00，因此，当连接串不指定时区时，也不会出现问题。

但是，如果在这种情况下，我们把数据库连接串的时区配置为真正的中国时区Aisa\Shanghai，那么，问题就出现了，根据上面的解释，Java应用最后应该会输出的时间会少13个小时，这就是出现时区问题的根本原因。

以下是jdbc获取时区配置的代码：

    /**
     * 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());
    }

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41

如果MySQL时区和serverTimezone配置不一样呢，这种情况会发生时区问题吗？比如把数据库时区配置为+9:00时区，然后把jdbc的url上的serverTimezone配置为与数据库不一致的GMT+8时区时，会发生时区问题吗？

比如一个时间值是东8区的2020-02-23 08:00:00，在Java代码中存储的是Date类型的绝对时间，由于serverTimeZone配置的是东8区，MySQL驱动会将这个Date对象转为字符串类型的2020-02-23 08:00:00，将sql发送给MySQL，然后MySQL数据库接收到这个时间字符串2020-02-23 08:00:00后，由于数据库时区配置是东9区，它会以东9区解析这个时间字符串，这时数据库保存的时间是东9区的2020-02-23 08:00:00，也就是东8区的2020-02-23 07:00:00，**这里需要注意的是，时间实际上已经出现了问题，保存的时间偏差了1个小时。**但是查询时MySQL返回了东9区的时间字符串2020-02-23 08:00:00，而Java又认为这是东8区的2020-02-23 08:00:00，和存储时一致。也是没有时区的问题。

但是，如果在这种情况下，我们把数据库连接串的时区也配置为+9:00时区，那么，问题就出现了，根据上面的解释，Java应用最后应该会输出的时间会多1个小时，这就是出现时区问题的根本原因。

总结

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应用它不会变。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/花生_TL007/article/detail/617357
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号