Spring Boot升级Hibernate 6后SQL Server ZonedDateTime字段校验失败求助

将Spring Boot应用迁移至Spring 3.2.1 + Hibernate 6.4.1版本（使用SQL Server和PostgreSQL作为持久化数据库）后，启动时出现Schema校验错误：

Schema-validation: wrong column type encountered in column [timestamp] in table [action_log]; found [timestamp (Types#BINARY)], but expecting [datetimeoffset(6) (Types#TIMESTAMP_UTC)]

对应表的SQL定义：

CREATE TABLE action_log
(
    action_log_id           bigint       NOT NULL,
    timestamp               timestamp    NOT NULL,
    ...
    PRIMARY KEY (action_log_id)
);

Java实体类定义：

@Entity
@Table(name = "action_log")
@SequenceGenerator(name = "action_log_id_generator", sequenceName = "action_log_id_seq")
public class ActionLogEntity {

    @Setter(AccessLevel.PRIVATE)
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "action_log_id_generator")
    @Column(name = "action_log_id")
    @TimeZoneStorage
    private Long id;

    @NotNull
    @Column(name = "timestamp", nullable = false, updatable = false)
    private ZonedDateTime timestamp;
    
    ...
}

已知Hibernate 6优化了时区处理，尝试过以下全局配置但未解决：

# Store everything as UTC
spring.jpa.properties.hibernate.jdbc.time_zone=UTC
spring.jpa.properties.hibernate.timezone.default_storage=NORMALIZE_UTC

改用datetime2类型时仍出现类似错误：

Schema-validation: wrong column type encountered in column [timestamp] in table [action_log]; found [datetime2 (Types#TIMESTAMP)], but expecting [datetimeoffset(6) (Types#TIMESTAMP_UTC)]

需求：配置Hibernate以接受SQL Server的timestamp/datetime2列类型，同时丢弃时区信息（始终以UTC存储时间）。

1. 实体字段级精准配置 #

在timestamp字段上明确指定数据库列类型，结合时区存储策略实现UTC归一化：

@NotNull
@Column(name = "timestamp", nullable = false, updatable = false, columnDefinition = "timestamp")
@TimeZoneStorage(TimeZoneStorageType.NORMALIZE_UTC)
private ZonedDateTime timestamp;

如果需要更自定义的转换逻辑，添加属性转换器：

@Converter(autoApply = false)
public class ZonedDateTimeToTimestampConverter implements AttributeConverter<ZonedDateTime, Timestamp> {
    @Override
    public Timestamp convertToDatabaseColumn(ZonedDateTime zonedDateTime) {
        if (zonedDateTime == null) {
            return null;
        }
        // 转换为UTC时间后存储
        return Timestamp.from(zonedDateTime.withZoneSameInstant(ZoneOffset.UTC).toInstant());
    }

    @Override
    public ZonedDateTime convertToEntityAttribute(Timestamp timestamp) {
        if (timestamp == null) {
            return null;
        }
        // 读取时转为UTC时区的ZonedDateTime
        return timestamp.toInstant().atZone(ZoneOffset.UTC);
    }
}

然后在字段上添加@Convert(converter = ZonedDateTimeToTimestampConverter.class)即可。

2. 全局配置适配 #

添加以下JPA配置，全局调整类型映射与时区处理：

# 指定SQL Server方言
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServerDialect
# 全局时区设为UTC
spring.jpa.properties.hibernate.jdbc.time_zone=UTC
# 全局时区存储策略归一化到UTC
spring.jpa.properties.hibernate.timezone.default_storage=NORMALIZE_UTC
# 可选：关闭严格类型校验（仅当上述配置无效时使用）
spring.jpa.properties.hibernate.schema_validate.types.basic.check=false

3. 修正SQL Server列类型（推荐） #

SQL Server的timestamp类型本质是行版本号（对应Types#BINARY），容易和时间类型混淆，建议将列类型改为datetime2(6)：

ALTER TABLE action_log ALTER COLUMN timestamp datetime2(6) NOT NULL;

实体字段同步调整：

@NotNull
@Column(name = "timestamp", nullable = false, updatable = false, columnDefinition = "datetime2(6)")
@TimeZoneStorage(TimeZoneStorageType.NORMALIZE_UTC)
private ZonedDateTime timestamp;

内容的提问来源于stack exchange，提问作者D. Kellenberger