[原创]Java项目统一UTC时间方案

作者：Gods_巨蚁

引言

近期团队的个别项目在进行框架升级后，部分时间值存在8小时误差，原因是错误的将数据库中的时间数据理解成了UTC时间（旧版本认为是北京时间）

考虑到未来项目对于时间理解的一致性，我决定将项目统一为使用UTC时间，经调研，形成本文

mysql数据库时区及时间时间类型说明

数据库时区

时间类型说明

统一UTC时间改造方案简述

统一时区设定

@Configuration
public class UTCTimeZoneConfiguration implementsServletContextListener{
    public voidcontextInitialized(ServletContextEvent event) {
        System.setProperty("user.timezone", "UTC");
        TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
    }

    public voidcontextDestroyed(ServletContextEvent event) {}
}

时间类型Joda DateTime的使用方式

public classEntity {
    @JsonSerialize(using = UTCDateTimeSerializer.class)
    @JsonDeserialize(using = UTCDateTimeDeserializer.class)
    privateDateTime dateTime;

    publicDateTime getDateTime() {
        returndateTime;
    }

    public voidsetDateTime(DateTime dateTime) {
        this.dateTime =dateTime;
    }
}

    @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的场景

    public void update(intid, DateTime dateTime) {
        String sql = "UPDATE " + TABLE_NAME + " SET datetime = ? WHERE id = ?";
        jdbcTemplate.update(sql, newTimestamp(dateTime.getMillis()), id);
    }

    public DateTime getDateTime(intid) {
        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) throwsSQLException {
                return new DateTime(rs.getTimestamp("datetime").getTime());
            }
        });
        return dateTimeList.size() > 0 ? dateTimeList.get(0) : null;
    }

Dao时间操作——针对数据库列为timestamp的场景

CREATE TABLEt1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

CREATE TABLEt1 (
  ts TIMESTAMP);

    public DateTime getTimestamp(intid) {
        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) throwsSQLException {
                return new DateTime(rs.getLong("unix_timestamp") * 1000);
            }
        });
        return dateTimeList.size() > 0 ? dateTimeList.get(0) : null;
    }

附录

Mysql时区设置

SET GLOBAL time_zone = SYSTEM;

SET GLOBAL time_zone = '+0:00';

SET GLOBAL time_zone = '+8:00';

set time_zone = '+0:00';

UTCDateTimeSerializer与UTCDateTimeDeserializer

public class UTCDateTimeSerializer extends JsonSerializer<DateTime>{
    @Override
    public voidserialize(DateTime dateTime,
                          JsonGenerator jsonGenerator,
                          SerializerProvider provider) throwsIOException {
        String dateTimeAsString =dateTime.withZone(DateTimeZone.UTC).toString(BecConstant.DATETIME_FORMAT);
        jsonGenerator.writeString(dateTimeAsString);
    }
}

public class UTCDateTimeDeserializer extends JsonDeserializer<DateTime>{
    @Override
    publicDateTime deserialize(JsonParser jsonParser, DeserializationContext deserializationContext)
            throwsIOException {
        JsonToken currentToken =jsonParser.getCurrentToken();
        if (currentToken ==JsonToken.VALUE_STRING) {
            String dateTimeAsString =jsonParser.getText().trim();
            returnDateTime.parse(dateTimeAsString).withZone(DateTimeZone.UTC);
        }
        return null;
    }
}