使用TIMESTAMP类型存储多时区时间

场景：

不同国家访问同一个服务器，存入同一个数据库。

时间字段存储时需要存一个带时区的时间，故用的TIMESTAMP。

实现逻辑：

数据库时间字段存的时区设置为默认UTC时间；

前端请求接口时获取用户所在时区信息，通过请求头将时区带到后端；

后端从请求头获取时区存储到上下文，接受前端传的时间参数并转换为utc时间去查询数据；

将数据时间字段转回为用户时区并返回。

1. 理解 TIMESTAMP 类型的时区特性

在 MySQL 中，TIMESTAMP 类型会自动将存储的值转换为 UTC 时间，并且在读取时再将其转换为当前会话的时区。因此，需要确保应用程序和数据库的时区设置一致，以避免数据显示不一致的问题。

2. 配置数据库连接时区

在 Spring Boot 项目的 application.yml 中，配置数据库连接的时区为 UTC，确保数据在存储和读取时使用统一的时区。

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/database_name?serverTimezone=UTC
    username: username
    password: password
    driver-class-name: com.mysql.cj.jdbc.Driver

3. 根据客户端时区进行查询

在查询数据库时，需要将客户端的时区信息转换为 UTC 时间，然后使用 UTC 时间进行查询。先创建一个时区工具类：

import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;

/**
 * @description 时区工具类
 */
public class TimeZoneUtil {

    /**
     * 将 localDateTime 转换为指定的时区时间
     */
    public static LocalDateTime convertToUtc(LocalDateTime localDateTime, String timeZone) {
        ZoneId clientZone = ZoneId.of(timeZone);
        ZonedDateTime zonedDateTime = localDateTime.atZone(clientZone);
        return zonedDateTime.withZoneSameInstant(ZoneId.of("UTC")).toLocalDateTime();
    }

    /**
     * 将数据库的时间转换为用户时区的时间
     */
    public static LocalDateTime convertFromUtc(LocalDateTime utcDateTime, String timeZone) {
        ZoneId clientZone = ZoneId.of(timeZone);
        ZonedDateTime zonedDateTime = utcDateTime.atZone(ZoneId.of("UTC"));
        return zonedDateTime.withZoneSameInstant(clientZone).toLocalDateTime();
    }

}

在 Service 层中使用转换后的 UTC 时间进行查询，并处理返回结果：

@Service
public class UserServiceImpl {

    @Autowried
    privat UserMapper UserMapper;

    @Override
    public List<SysUser> selectUserByCreateTime(LocalDateTime startTime, LocalDateTime endTime) {
        // 获取用户时区
        String timeZone = UserSessionUtils.getTimeZong();
        // 将客户端时间参数转换为UTC时间
        LocalDateTime utcStartTime = TimeZoneUtils.convertToUtc(startTime, timeZone);
        LocalDateTime utcEndTime = TimeZoneUtils.convertToUtc(endTime, timeZone);
        LambdaQueryWrapper<SysUser> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.between(SysUser::getCreateTime, utcStartTime, utcEndTime);
        List<SysUser> userList = userMapper.selectList(queryWrapper);
        // 将 userList 的 create_time 转换为客户端的时区
        return userList.stream().peek(user -> {
            LocalDateTime utcDateTime = user.getCreateTime();
            LocalDateTime clientDateTime = TimeZoneUtils.convertFromUtc(utcDateTime, timeZone);
            user.setCreateTime(clientDateTime);
        }).collect(Collectors.toList());
    }
}