存在資料庫的utc時間_資料庫時間不是utc時間 - CSDN

作者：Gods_巨蟻

引言

近期團隊的個別項目在進行框架升級後，部分時間值存在8小時誤差，原因是錯誤的將資料庫中的時間數據理解成了UTC時間（舊版本認為是北京時間）

考慮到未來項目對於時間理解的一致性，我決定將項目統一為使用UTC時間，經調研，形成本文

 

mysql資料庫時區及時間時間類型說明

資料庫時區

mysql資料庫擁有時區設置，默認使用系統時區

可通過如下語句查詢當前時區

show variables like '%time_zone%';

 

下圖為我個人機器上mysql資料庫時區設置：

 

 

項目線上資料庫時區設置如下:

 

可見資料庫使用系統時間CST——China Standard Time UTC+8:00 中國沿海時間(北京時間)

 

時間類型說明

datetime

實際格式儲存（Just stores what you have stored and retrieves the same thing which you have stored.）

與時區無關（It has nothing to deal with the TIMEZONE and Conversion.）

 

timestamp

值以UTC毫秒數保存（ it stores the number of milliseconds）

存儲及檢索時根據當前時區設置，對時間數值做轉換

 

由於timestamp與時區相關，且線上資料庫時區設置為北京時間（即UTC+8:00）。因此，當資料庫中使用了timestamp列，若使用不當，統一UTC格式時間改造將很可能會引入錯誤！ 後面詳述理由

 

統一UTC時間改造方案簡述

統一時區設定

項目新框架中通過UTCTimeZoneConfiguration類型，在項目初始化時設置當前進程的默認時區

@Configurationpublic class UTCTimeZoneConfiguration implements ServletContextListener{ public void contextInitialized(ServletContextEvent event) { System.setProperty("user.timezone", "UTC"); TimeZone.setDefault(TimeZone.getTimeZone("UTC")); } public void contextDestroyed(ServletContextEvent event) {}}

  

時間類型Joda DateTime的使用方式

日期時間類型可以使用 java.util.Date，但推薦使用更為方便的joda DateTime，本節介紹joda DateTime 序列化/反序列化使用方式

 

Joda DateTime 類型用於定義接口輸入輸出參數，需進行序列化/反序列化操作。與原生的Date類型不同，DateTime需要做一點額外處理

 

1、Model類型的日期欄位使用類型DateTime替代Date

實例代碼如下

public class Entity { @JsonSerialize(using = UTCDateTimeSerializer.class) @JsonDeserialize(using = UTCDateTimeDeserializer.class) private DateTime dateTime; public DateTime getDateTime() { return dateTime; } public void setDateTime(DateTime dateTime) { this.dateTime = dateTime; }}

  其中UTCDateTimeSerializer與UTCDateTimeDeserializer類的實現見附錄

 

2、Get請求接受時間參數

此時，一種有效的處理方式是使用字符串接受日期參數，如下：

@RequestMapping(value = "/xxx", method = RequestMethod.GET) public CommonResponse getXxx(@RequestParam(value = "beginTime") String beginTimeText, @RequestParam(value = "endTime") String endTimeText) { DateTime beginTime = DateTime.parse(beginTimeText).withZone(DateTimeZone.UTC); DateTime endTime = DateTime.parse(endTimeText).withZone(DateTimeZone.UTC); ... }

  

Dao時間操作——針對資料庫列為datetime的場景

以Joda DateTime類型舉例說明使用方法，某Dao類型中存在的兩個方法如下：

public void update(int id, DateTime dateTime) { String sql = "UPDATE " + TABLE_NAME + " SET datetime = ? WHERE id = ?"; jdbcTemplate.update(sql, new Timestamp(dateTime.getMillis()), id); } public DateTime getDateTime(int id) { String sql = "SELECT datetime FROM " + TABLE_NAME + " WHERE id = ?"; List<DateTime> dateTimeList = jdbcTemplate.query(sql, new Object[] {id}, new RowMapper<DateTime>() { @Override public DateTime mapRow(ResultSet rs, int rowNum) throws SQLException { return new DateTime(rs.getTimestamp("datetime").getTime()); } }); return dateTimeList.size() > 0 ? dateTimeList.get(0) : null; }

 插入或更新數據，傳遞的時間參數請使用 new Timestamp(dateTime.getMillis())

讀取時間參數，使用new DateTime(rs.getTimestamp("datetime").getTime())

 

Dao時間操作——針對資料庫列為timestamp的場景

資料庫timestamp類型適合用來記錄數據的最後修改時間

其他場景建議使用datetime或者int

 

方案一 更改會話時區為UTC時間

對timestamp列的操作與datetime列的操作不做區分，此時需要設置數據連接會話的時區，默認為北京時間，需要設置為UTC時間，通過如下語句設置

set time_zone = '+0:00';

 

實際項目中使用資料庫連接池，創建datasource後使用如下方式設置時區，將對所有連接生效

dataSource.setInitSQL("set time_zone = '+0:00'");

 

經此操作後，時區統一為UTC時間，Dao中時間操作，無需對timestamp做特殊處理

 

方案二 不更改會話時區

由於不更改時區，timestamp類型數據的使用存在一定限制

1、 如何更新timestamp數據

對於資料庫表中的timestamp列，其值的更新應當由資料庫自行維護，在create table時設置，如下：

CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

 可簡寫如下

CREATE TABLE t1 ( ts TIMESTAMP);

  不允許程序自主更新timstamp列數據

線上資料庫時區為北京時間，其接受到的日期數據被視為北京時間，而上層程序業務邏輯統一使用UTC時間，時區不統一。因此避免資料庫記錄的日期數據理解不一致，不允許程序通過寫操作sql語句更新timestamp列

 

下圖數據為本人實測數據，timestamp列由程序進行更新，update_time列則由資料庫自動更新

 

前者顯示的是UTC時間，看似合理，實則錯誤，資料庫內部存儲時間為UTC-8:00

update_time符合資料庫時區設置，返回北京時間，內部實際存儲UTC時間

 

2、 如何讀取timestamp數據

為避免從資料庫中獲取時區相關時間（北京時間），強制使用UTC時間，使用函數UNIX_TIMESTAMP獲取1970年至今秒數，轉換成DateTime時乘以1000轉變為毫秒

public DateTime getTimestamp(int id) { String sql = "SELECT UNIX_TIMESTAMP(update_time) as unix_timestamp FROM " + TABLE_NAME + " WHERE id = ?"; List<DateTime> dateTimeList = jdbcTemplate.query(sql, new Object[] {id}, new RowMapper<DateTime>() { @Override public DateTime mapRow(ResultSet rs, int rowNum) throws SQLException { return new DateTime(rs.getLong("unix_timestamp") * 1000); } }); return dateTimeList.size() > 0 ? dateTimeList.get(0) : null; }

 

附錄

Mysql時區設置

設置全局時區，需要管理員權限

使用本機系統時區

SET GLOBAL time_zone = SYSTEM;

使用UTC時間

SET GLOBAL time_zone = '+0:00';

使用北京時間

SET GLOBAL time_zone = '+8:00';

設置當前連接會話時區

 

UTCDateTimeSerializer與UTCDateTimeDeserializer

UTCDateTimeSerializer 完成DateTime對象到UTC時間字符串的轉換，格式為：yyyy-MM-ddTHH:mm:ssZ

UTCDateTimeDeserializer 完成時間字符串到DateTime對象的轉換，轉換為UTC時區

 

具體實現如下：

public class UTCDateTimeSerializer extends JsonSerializer<DateTime> { @Override public void serialize(DateTime dateTime, JsonGenerator jsonGenerator, SerializerProvider provider) throws IOException { String dateTimeAsString = dateTime.withZone(DateTimeZone.UTC).toString(BecConstant.DATETIME_FORMAT); jsonGenerator.writeString(dateTimeAsString); }}public class UTCDateTimeDeserializer extends JsonDeserializer<DateTime> { @Override public DateTime deserialize(JsonParser jsonParser, DeserializationContext deserializationContext) throws IOException { JsonToken currentToken = jsonParser.getCurrentToken(); if (currentToken == JsonToken.VALUE_STRING) { String dateTimeAsString = jsonParser.getText().trim(); return DateTime.parse(dateTimeAsString).withZone(DateTimeZone.UTC); } return null; }}