From: http://www.cnblogs.com/jason1990/archive/2018/11/28/10032181.html

項(xiàng)目中遇到存儲(chǔ)的時(shí)間戳與真實(shí)時(shí)間相差14小時(shí)的現(xiàn)象,以下為解決步驟.

問(wèn)題

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;

以上為數(shù)據(jù)庫(kù)建表語(yǔ)句,其中created_time是插入記錄時(shí)自動(dòng)設(shè)置,recovery_time需要手動(dòng)進(jìn)行設(shè)置.
測(cè)試時(shí)發(fā)現(xiàn),created_time為正確的北京時(shí)間,然而recovery_time則與設(shè)置時(shí)間相差14小時(shí).

嘗試措施

jvm時(shí)區(qū)設(shè)置

//設(shè)置jvm默認(rèn)時(shí)間 System.setProperty("user.timezone", "UTC");

數(shù)據(jù)庫(kù)時(shí)區(qū)查詢

查看數(shù)據(jù)庫(kù)時(shí)區(qū)設(shè)置:

show variables like '%time_zone%'; --- 查詢結(jié)果如下所示: --- system_time_zone: CST --- time_zone:SYSTEM

查詢CST發(fā)現(xiàn)其指代比較混亂,有四種含義(參考網(wǎng)址:https://juejin.im/post/5902e087da2f60005df05c3d):

• 美國(guó)中部時(shí)間 Central Standard Time (USA) UTC-06:00
• 澳大利亞中部時(shí)間 Central Standard Time (Australia) UTC+09:30
• 中國(guó)標(biāo)準(zhǔn)時(shí) China Standard Time UTC+08:00
• 古巴標(biāo)準(zhǔn)時(shí) Cuba Standard Time UTC-04:00

此處發(fā)現(xiàn)如果按照美國(guó)中部時(shí)間進(jìn)行推算,相差14小時(shí),與Bug吻合.

驗(yàn)證過(guò)程

MyBatis轉(zhuǎn)換

代碼中,時(shí)間戳使用Instant進(jìn)行存儲(chǔ),因此跟蹤package org.apache.ibatis.type下的InstantTypeHandler.

@UsesJava8 public class InstantTypeHandler extends BaseTypeHandler<Instant> {@Overridepublic void setNonNullParameter(PreparedStatement ps, int i, Instant parameter, JdbcType jdbcType) throws SQLException {ps.setTimestamp(i, Timestamp.from(parameter));}//...代碼shenglve }

調(diào)試時(shí)發(fā)現(xiàn)parameter為正確的UTC時(shí).
函數(shù)中調(diào)用Timestamp.from將Instant轉(zhuǎn)換為Timestamp實(shí)例,檢查無(wú)誤.

/*** 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;

繼續(xù)跟蹤setTimestamp接口,其具體解釋見代碼注釋.

Sql Driver轉(zhuǎn)換

項(xiàng)目使用com.mysql.cj.jdbc驅(qū)動(dòng),跟蹤其setTimestamp在ClientPreparedStatement類下的具體實(shí)現(xiàn)(PreparedStatementWrapper類下實(shí)現(xiàn)未進(jìn)入).

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

繼續(xù)跟蹤上端代碼中的getQueryBindings().setTimestamp()實(shí)現(xiàn)(com.mysql.cj.ClientPreparedQueryBindings).

@Overridepublic 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 positionsfractionalLength = 6;}x = TimeUtil.adjustTimestampNanosPrecision(x, fractionalLength, !this.session.getServerSession().isServerTruncatesFracSecs());//注意此處時(shí)區(qū)轉(zhuǎn)換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);}}

注意此處時(shí)區(qū)轉(zhuǎn)換,會(huì)調(diào)用如下語(yǔ)句獲取默認(rèn)時(shí)區(qū):

this.session.getServerSession().getDefaultTimeZone()

獲取TimeZone數(shù)據(jù),具體如下圖所示:

檢查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表示本地時(shí)間與UTC時(shí)的時(shí)間間隔(ms).
計(jì)算數(shù)值offset,發(fā)現(xiàn)其表示美國(guó)中部時(shí)間,即UTC-06:00.

• Driver推斷Session時(shí)區(qū)為UTC-6;
• Driver將Timestamp轉(zhuǎn)換為UTC-6的String;
• MySql認(rèn)為Session時(shí)區(qū)在UTC+8,將String轉(zhuǎn)換為UTC+8.

因此,最終結(jié)果相差14小時(shí),bug源頭找到.

解決方案

參照https://juejin.im/post/5902e087da2f60005df05c3d.

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)

告知運(yùn)維設(shè)置時(shí)區(qū),重啟MySql服務(wù),問(wèn)題解決.

此外,作為防御措施,可以在jdbc url中設(shè)置時(shí)區(qū)(如此設(shè)置可以不用修改MySql配置):

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

此時(shí),就告知連接進(jìn)行時(shí)區(qū)轉(zhuǎn)換,并且時(shí)區(qū)為UTC+8.

PS:
如果您覺(jué)得我的文章對(duì)您有幫助，可以掃碼領(lǐng)取下紅包，謝謝！

總結(jié)

以上是生活随笔為你收集整理的SpringBoot时间戳与MySql数据库记录相差14小时排错的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。