The entities in my application have a lot of Instant fields. I don't care about time zones, everything is in UTC. I am using Postgresql 13.2. Hibernate 5 maps those fields in the CREATE TABLE statements to timestamp, which Postgresql interprets as "timestamp without time zone". This is the desired behavior.

However, after upgrading the app to use Hibernate 6, the fields in the CREATE TABLE statements are now "timestamp(6) with time zone". Liquibase then generates a diff with lot of false changes.

Is there a way to tell Hibernate 6 to continue mapping Instant fields to timestamp (without time zone)?

I tried setting the timezone of the postgresql server to "Etc/UTC", it did not help. Neither did setting

spring.jpa.properties.hibernate.jdbc.time_zone=UTC

in application.properties.

CodePudding user response：

First: the correct data type to use to represent a timestamp in UTC in Postgres is timestamp with time zone, which does not actually store any time zone information, it just stores timestamps normalized to UTC. From the Postgres docs:

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time...). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

On the other hand, the SQL type timestamp is more like a LocalDateTime in Java, it is not by nature a UTC datetime.

Second: if you don't like the SQL column type that Hibernate uses by default, then you can of course change it, either in the JPA standard way:

@Column(columnDefinition="timestamp")

or in the IMO much better native Hibernate way:

@JdbcTypeCode(TIMESTAMP)

or:

@JdbcType(TimestampJdbcType.class)

There is even an (incubating) global setting defined by AvailableSettings.PREFERRED_INSTANT_JDBC_TYPE:

hibernate.type.preferred_instant_jdbc_type=TIMESTAMP

However, I do not recommend you use any of these settings, since, as I said, the correct SQL type to use on Postgres is the one Hibernate uses by default.