SpringBoot时间戳与MySql数据库记录相差14小时排错

项目中遇到存储的时间戳与真实时间 相差14小时
的现象,以下为解决步骤.

问题

CREATE TABLE `incident` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `recovery_time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;

以上为数据库建表语句,其中 created_time
是插入记录时自动设置, recovery_time
需要手动进行设置. 测试时发现, created_time
为正确的北京时间,然而 recovery_time
则与设置时间相差14小时.

尝试措施

jvm时区设置

//设置jvm默认时间
System.setProperty("user.timezone", "UTC");

数据库时区查询

查看数据库时区设置:

show variables like '%time_zone%';
--- 查询结果如下所示:
--- system_time_zone: CST
--- time_zone:SYSTEM

查询 CST
发现其指代比较混乱,有四种含义(参考网址:https://juejin.im/post/5902e087da2f60005df05c3d):

• 美国中部时间 Central Standard Time (USA) UTC-06:00

• 澳大利亚中部时间 Central Standard Time (Australia) UTC+09:30

• 中国标准时 China Standard Time UTC+08:00

• 古巴标准时 Cuba Standard Time UTC-04:00

此处发现如果按照 美国中部时间
进行推算, 相差14小时
,与Bug吻合.

验证过程

MyBatis转换

代码中,时间戳使用 Instant
进行存储,因此跟踪 packageorg.apache.ibatis.type
下的 InstantTypeHandler
.

@UsesJava8
public class InstantTypeHandler extends BaseTypeHandler<Instant> {

  @Override
  public void setNonNullParameter(PreparedStatement ps, int i, Instant parameter, JdbcType jdbcType) throws SQLException {
    ps.setTimestamp(i, Timestamp.from(parameter));
  }

  //...代码shenglve
}

调试时发现 parameter
为正确的 UTC
时. 函数中调用 Timestamp.from
将 Instant
转换为 Timestamp
实例,检查无误.

    /**
     * Sets the designated parameter to the given <code>java.sql.Timestamp</code> value.
     * The driver
     * converts this to an SQL <code>TIMESTAMP</code> value when it sends it to the
     * database.
     *
     * @param parameterIndex the first parameter is 1, the second is 2, ...
     * @param x the parameter value
     * @exception SQLException if parameterIndex does not correspond to a parameter
     * marker in the SQL statement; if a database access error occurs or
     * this method is called on a closed <code>PreparedStatement</code>     */
    void setTimestamp(int parameterIndex, java.sql.Timestamp x)
            throws SQLException;

继续跟踪 setTimestamp
接口,其具体解释见代码注释.

Sql Driver转换

项目使用 com.mysql.cj.jdbc
驱动,跟踪其 setTimestamp
在 ClientPreparedStatement
类下的具体实现( PreparedStatementWrapper
类下实现未进入).

    @Override
    public void setTimestamp(int parameterIndex, Timestamp x) throws java.sql.SQLException {
        synchronized (checkClosed().getConnectionMutex()) {
            ((PreparedQuery<?>) this.query).getQueryBindings().setTimestamp(getCoreParameterIndex(parameterIndex), x);
        }
    }

继续跟踪上端代码中的 getQueryBindings().setTimestamp()
实现( com.mysql.cj.ClientPreparedQueryBindings
).

    @Override
    public void setTimestamp(int parameterIndex, Timestamp x, Calendar targetCalendar, int fractionalLength) {
        if (x == null) {
            setNull(parameterIndex);
        } else {

            x = (Timestamp) x.clone();

            if (!this.session.getServerSession().getCapabilities().serverSupportsFracSecs()
                    || !this.sendFractionalSeconds.getValue() && fractionalLength == 0) {
                x = TimeUtil.truncateFractionalSeconds(x);
            }

            if (fractionalLength < 0) {
                // default to 6 fractional positions
                fractionalLength = 6;
            }

            x = TimeUtil.adjustTimestampNanosPrecision(x, fractionalLength, !this.session.getServerSession().isServerTruncatesFracSecs());

            //注意此处时区转换
            this.tsdf = TimeUtil.getSimpleDateFormat(this.tsdf, "''yyyy-MM-dd HH:mm:ss", targetCalendar,
                    targetCalendar != null ? null : this.session.getServerSession().getDefaultTimeZone());

            StringBuffer buf = new StringBuffer();
            buf.append(this.tsdf.format(x));
            if (this.session.getServerSession().getCapabilities().serverSupportsFracSecs()) {
                buf.append('.');
                buf.append(TimeUtil.formatNanos(x.getNanos(), 6));
            }
            buf.append('\'');

            setValue(parameterIndex, buf.toString(), MysqlType.TIMESTAMP);
        }
    }

注意此处时区转换,会调用如下语句获取默认时区:

this.session.getServerSession().getDefaultTimeZone()

获取 TimeZone
数据,具体如下图所示:

检查 TimeZone
类中 offset
含义,具体如下所示:

    /**
     * Gets the time zone offset, for current date, modified in case of
     * daylight savings. This is the offset to add to UTC to get local time.
     * <p>
     * This method returns a historically correct offset if an
     * underlying <code>TimeZone</code> implementation subclass
     * supports historical Daylight Saving Time schedule and GMT
     * offset changes.
     *
     * @param era the era of the given date.
     * @param year the year in the given date.
     * @param month the month in the given date.
     * Month is 0-based. e.g., 0 for January.
     * @param day the day-in-month of the given date.
     * @param dayOfWeek the day-of-week of the given date.
     * @param milliseconds the milliseconds in day in <em>standard</em>
     * local time.
     *
     * @return the offset in milliseconds to add to GMT to get local time.
     *
     * @see Calendar#ZONE_OFFSET
     * @see Calendar#DST_OFFSET
     */
    public abstract int getOffset(int era, int year, int month, int day,
                                  int dayOfWeek, int milliseconds);

offset
表示 本地时间
与 UTC
时的 时间间隔(ms)
. 计算数值 offset
,发现其表示 美国中部时间
,即 UTC-06:00
.

• Driver
  推断 Session
  时区为 UTC-6
  ;

• Driver
  将 Timestamp
  转换为 UTC-6
  的 String
  ;

• MySql
  认为 Session
  时区在 UTC+8
  ,将 String
  转换为 UTC+8
  .

因此,最终结果相差14小时, bug
源头找到.

解决方案

解决方案当然是熟练的告知运维，修改 mysql
的时区即可.

mysql> set global time_zone = '+08:00';
Query OK, 0 rows affected (0.00 sec)

mysql> set time_zone = '+08:00';
Query OK, 0 rows affected (0.00 sec)

告知运维设置时区,重启 MySql
服务,问题解决.

此外,作为防御措施,可以在 jdbc url
中设置时区(如此设置可以不用修改 MySql
配置):

jdbc:mysql://localhost:3306/table_name?useTimezone=true&serverTimezone=GMT%2B8

此时,就告知连接进行 时区转换
,并且时区为 UTC+8
.