Hibernate 6 implicitly changes time zones of read data for Instant

I have problem with read data by hibernate from postgresql db

I am migrating a legacy project to Spring Boot 3 and Hibernate 6.5.3.Final

I have entity:

@Data
@Entity
@SuperBuilder(toBuilder = true)
@AllArgsConstructor
@NoArgsConstructor
public class TestEnity {
    @NonNull
    private UUID id;
    @NonNull
    private Instant createDate;
}

Add record by EntityManager (in my timezone now 12 am, UTC+02:00) with property spring.jpa.properties.hibernate.type.preferred_instant_jdbc_type=TIMESTAMP:

id                                      |   create_date
----------------------------------------------------------------------
3ceca36e-710f-4ea3-223b-1118b0796c7f    |   2025-04-10 12:20:30.510548
3ceca36e-710f-4ea3-223b-2228b0796c7f    |   2025-04-09 12:20:30.510548
3ceca36e-710f-4ea3-223b-3338b0796c7f    |   2025-04-08 12:20:30.510548

And I use querydsl for get records:

SQLQueryFactory sqlQueryFactory = new SQLQueryFactory(GenaratorQsqlUtils.createConfiguration(), dataSource);

BooleanBuilder booleanBuilder = new BooleanBuilder();
booleanBuilder.and(getDateFrom(instantFrom));
booleanBuilder.and(getDateTo(instantTo));
    
long count = sqlQueryFactory.select()
                .from(testE)
                .where(booleanBuilder) //createDate >= 2025-04-09T12:19:58Z && createDate <= 2025-04-10T12:29:58Z
                .fetchCount();

And I wait get 2 record 1118b0796c7f and 2228b0796c7f, but hibernate find only 1118b0796c7f and in debug I see:

id: 3ceca36e-710f-4ea3-223b-1118b0796c7f;   create_date: 2025-04-10 10:20:30.510548

If I update record 2228b0796c7f column create_date for 2025-04-09 14:20:30.510548 (that is, in the current offset recorded in the database, I added another one +02) than I get 2 records

In hibernate 5 all was OK

Now, in hibernate 6 I resolve problem with write data - I add property spring.jpa.properties.hibernate.type.preferred_instant_jdbc_type=TIMESTAMP, and in database I see rows with time in my timezone

But when I read data - they in UTC

How I can fix it?

I just need to ensure backward compatibility and write and read data in my time zone, that is, see 2025-04-10 12:20:30.510548 in the database and receive 2025-04-10 12:20:30.510548 when reading in the application.

For writing, I set the parameter spring.jpa.properties.hibernate.type.preferred_instant_jdbc_type=TIMESTAMP, but when reading, the time recorded in the database shifts to the time zone and I get 2025-04-10 10:20:30.510548, but need 2025-04-10 12:20:30.510548

Maybe my approach with the parameter is wrong in general and I need to use something else? Or how to configure hibernate so that it does not make an implicit conversion of data from the database to the UTC format?

Solution

As per my understanding, your use case does not involve time zone-specific behavior, and your database column is defined as TIMESTAMP WITHOUT TIME ZONE. In that case, the most appropriate and consistent mapping in your entity is to use java.time.LocalDateTime, which also does not carry any time zone context.

Here’s how your entity should look:

@Data
@Entity
@SuperBuilder(toBuilder = true)
@AllArgsConstructor
@NoArgsConstructor
public class TestEntity {

    @Id
    @NonNull
    private UUID id;

    @NonNull
    private LocalDateTime createDate;
}

This setup ensures that the timestamp values you store and retrieve from the database remain unchanged, without any implicit time zone conversions.

However, if you prefer to continue using Instant, you will need to update your database schema to store timestamps with explicit time zone information. You can achieve this by altering the column to TIMESTAMPTZ using the following query:

ALTER TABLE test_entity
ALTER COLUMN create_date TYPE TIMESTAMPTZ;

Important Note:
When you alter the column to TIMESTAMPTZ, PostgreSQL interprets existing TIMESTAMP values as if they are in the current PostgreSQL session’s time zone (typically your system’s local time). It then converts them to UTC and stores them accordingly.

For example, if a record previously had:

create_date = '2025-04-10 12:20:30'  -- interpreted as local time (e.g., UTC+2)

After running the ALTER command, assuming the session time zone is UTC+2, PostgreSQL will convert and store it as:

create_date = '2025-04-10 10:20:30+00'  -- stored in UTC

This behavior is essential to understand, as it may affect how your existing data is interpreted after the column is altered.

Also you don't need to enter this line in your application.properties file
spring.jpa.properties.hibernate.type.preferred_instant_jdbc_type=TIMESTAMP