夏令时期间Mariadb存储ZonedDateTime出现重复键问题求助

问题原因 #

MariaDB的timestamp和datetime类型不存储时区信息，仅保存本地时间值。当你将转换为GMT（UTC）的ZonedDateTime存入数据库时，JDBC驱动会自动将UTC时间转换为数据库会话/服务器时区的本地时间。在夏令时回拨场景（比如10月30日欧洲时区调钟），两个不同的UTC时间会被转换为数据库时区下的同一个本地时间，触发唯一约束冲突。

以你的数据为例：

• 2022-10-30T02:00:00+02:00 转换为UTC是 2022-10-30T00:00:00Z
• 2022-10-30T02:00:00+01:00 转换为UTC是 2022-10-30T01:00:00Z

若数据库时区为+03:00，这两个UTC时间会被统一转换为2022-10-30 03:00:00，从而触发唯一约束报错。

可行解决方案 #

1. 存储Unix时间戳（最稳妥） #

将时间转换为毫秒级Unix时间戳，用整数类型存储，完全规避时区转换问题：

• 数据库字段类型：BIGINT，添加唯一约束
• Kotlin转换代码：

val timestampMillis = ZonedDateTime.parse(timestamp)
                                  .toInstant()
                                  .toEpochMilli()

每个时间点对应唯一的时间戳，不会出现重复冲突。

2. 将数据库时区设置为UTC #

强制数据库使用UTC时区存储时间，彻底避免夏令时影响：

• 全局设置：修改MariaDB配置文件（my.cnf/my.ini），添加：

  default-time-zone = '+00:00'
  

  重启数据库生效。
• 会话级设置：每次连接数据库后执行SQL：

  SET time_zone = '+00:00';
  

  这种方式下，你转换后的GMT时间会直接以UTC格式存入数据库，每个UTC时间对应唯一的数据库时间值。

3. 存储UTC格式字符串 #

将时间转换为UTC的ISO 8601字符串存储：

• 数据库字段类型：VARCHAR(30)，添加唯一约束
• Kotlin转换代码：

val utcTimestampStr = ZonedDateTime.parse(timestamp)
                                  .withZoneSameInstant(ZoneId.of("UTC"))
                                  .format(DateTimeFormatter.ISO_INSTANT)

存储的字符串如2022-10-30T00:00:00Z，每个原时间对应唯一的字符串，不会重复。

4. 联合时区字段做唯一约束 #

修改表结构，新增时区字段，用(timestamp, timezone)联合唯一约束区分重复本地时间：

• 数据库表添加timezone VARCHAR(10)字段，创建联合唯一索引：

  CREATE UNIQUE INDEX idx_timestamp_timezone ON your_table(timestamp, timezone);
  

• Kotlin代码中同时存储时间和时区：

val zonedDt = ZonedDateTime.parse(timestamp)
val timestampLocal = zonedDt.toLocalDateTime()
val timezone = zonedDt.offset.id // 或时区ID如"Europe/Paris"

这种方式需要修改实体类和表结构，查询时需结合时区做转换处理。

内容的提问来源于stack exchange，提问作者user3052078