How to properly configure timestamp handling for TIMESTAMP columns in Hibernate6/Spring Boot 3.0?-CodePudding

We're currently migrating a Spring Boot 2 application to Spring Boot 3 (3.0.2) which includes moving to Hibernate 6 (6.1.6).

All Timestamps are normalized to UTC and saved to the underlying OracleDB using columns of the TIMESTAMP data type (without timezones). To make Hibernate use UTC we set the jpa.properties.hibernate.jdbc.time_zone configuration property to true within our application.yaml.

After moving to Spring Boot 3 and Hibernate 6 (which introduces a more specific configuration for timestamp conversion) the migration of timestamps doesn't work as expected anymore:

When looking up entities by timestamp attributes, they won't be found.

A short example:
Saving an entity with a java.time.Instant timestamp of 2023-12-13T00:00:00Z to the database creates a new record as expected.
However, when trying to retrieve the record using the same timestamp the data repository will return an empty result. It therefore seems that the timestamp conversion differs between saving and querying.

We tried to adjust this behaviour using the configuration property spring.jpa.properties.hibernate.timezone.default_storage but no matter what value we set, the behaviour kept being the same. We also tried different combinations of the jdbc.time_zone and timezone.default_storage config properties without recognizing any effects on the application's behaviour. We initially thought that the properties for whatever reason wouldn't be applied, but assigning invalid values raised exceptions on application startup.

The same code works like a charm when using a H2 database.

The relevant part of the application.yaml we're using is as follows:

spring:
  datasource:
    driverClassName: oracle.jdbc.OracleDriver
    url: jdbc:oracle:thin:@localhost:1521:xe
    username: [dbuser]
    password: [dbpassword]
    type: org.springframework.jdbc.datasource.SimpleDriverDataSource
  jpa:
    show-sql: false
    generate-ddl: true
    hibernate.ddl-auto: none
    task:
    scheduling:
      pool:
        size: 10
    properties:
      hibernate:
        jdbc:
          time_zone: UTC
        timezone:
          default_storage: NORMALIZE_UTC

An example entity:

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import java.time.Instant;
import java.util.UUID;
import lombok.Getter;
import lombok.Setter;

@Setter
@Getter
@Entity
@Table(name ="entity")
public class DemoEntity {

  @Id
  @Column(name = "`id`")
  UUID id;

  @Column(name = "`demo_timestamp`" ,columnDefinition = "TIMESTAMP")
  private Instant timestamp;

  public DemoEntity() {
    this.id = UUID.randomUUID();
  }

}

The repository:

import java.time.Instant;
import java.util.Optional;
import java.util.UUID;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;

@Repository
public interface EntityRepository extends JpaRepository<DemoEntity, UUID>, JpaSpecificationExecutor<DemoEntity> {

  Optional<DemoEntity> findAllByTimestamp(Instant timestamp);
}

The service showcasing the observed behaviour:

import java.time.Instant;
import java.util.Optional;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.event.ContextRefreshedEvent;
import org.springframework.context.event.EventListener;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
@Slf4j
public class EntityService {

  private final EntityRepository repository;

  @Autowired
  public EntityService(EntityRepository repository) {
    this.repository = repository;
  }

  @EventListener(ContextRefreshedEvent.class)
  @Transactional
  public void init() {
    Instant timestamp = Instant.parse("2022-12-31T23:00:00Z");

    Optional<DemoEntity> findResult = repository.findAllByTimestamp(timestamp);

    if(findResult.isPresent()) {
      log.info("Entity was found for timestamp {}", timestamp);
      return;
    }

    log.info("No entity was found for timestamp {}, creating one", timestamp);

    DemoEntity demoEntity = new DemoEntity();
    demoEntity.setTimestamp(timestamp);

    this.repository.save(demoEntity);
  }
}

The service keeps creating new records with correct timestamps since it fails to look up the persisted timestamps:

The entity is created properly...
...but not found by the SQL query afterwards...

2023-01-26T07:20:47.986 01:00  INFO 1274 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2023-01-26T07:20:48.105 01:00 DEBUG 1274 --- [  restartedMain] org.hibernate.SQL                        : select d1_0."id",d1_0."demo_timestamp" from entity d1_0 where d1_0."demo_timestamp"=?
2023-01-26T07:20:48.106 01:00 TRACE 1274 --- [  restartedMain] org.hibernate.orm.jdbc.bind              : binding parameter [1] as [TIMESTAMP_UTC] - [2022-12-31T23:00:00Z]
2023-01-26T07:20:48.130 01:00  INFO 1274 --- [  restartedMain] com.example.demo.EntityService           : No entity was found for timestamp 2022-12-31T23:00:00Z, creating one
2023-01-26T07:20:48.138 01:00 DEBUG 1274 --- [  restartedMain] org.hibernate.SQL                        : select d1_0."id",d1_0."demo_timestamp" from entity d1_0 where d1_0."id"=?
2023-01-26T07:20:48.138 01:00 TRACE 1274 --- [  restartedMain] org.hibernate.orm.jdbc.bind              : binding parameter [1] as [BINARY] - [1ccd9b88-4d18-416a-938d-d8c3fb6dac7d]
2023-01-26T07:20:48.150 01:00 DEBUG 1274 --- [  restartedMain] org.hibernate.SQL                        : insert into entity ("demo_timestamp", "id") values (?, ?)
2023-01-26T07:20:48.150 01:00 TRACE 1274 --- [  restartedMain] org.hibernate.orm.jdbc.bind              : binding parameter [1] as [TIMESTAMP_UTC] - [2022-12-31T23:00:00Z]
2023-01-26T07:20:48.150 01:00 TRACE 1274 --- [  restartedMain] org.hibernate.orm.jdbc.bind              : binding parameter [2] as [BINARY] - [1ccd9b88-4d18-416a-938d-d8c3fb6dac7d]

...resulting in another entity being created

We also found, that looking up the entities as if they were in fact using not UTC but our local timezone CET, i.e. UTC 1, delivered the expected results. More specifically, looking up records for an Instant of 2022-31-12T22:00:00Z returned the entities having a timestamp of 2022-31-12T23:00:00Z. Moreover everything works fine when setting the JVM timezone using TimeZone.setDefault(TimeZone.getTimeZone("UTC")); or -Duser.timezone=UTC.

It seems that UTC timestamps represented as Instants are actually converted to local time when being queried but are handled properly when being written to the database.

Do we miss anything in our configuration or is there a bug in Spring/Hibernate that causes this odd behaviour?

As far as I know the default behaviour of Hibernate 6 should match the one provided by version 5 so that no configuration changes should be required at all to preserve the previous behaviour.

CodePudding user response：

OK, so, look, after my comment I was really hoping that you would make an attempt to reproduce the problem outside of Spring, so that we could establish what the cause of it is.

But anyway, I have now I've managed to reproduce it with the following setup:

set the JVM timezone to EST and the Oracle server timezone to UTC
do not set hibernate.jdbc.time_zone, so that Hibernate doesn't know there's a mismatch between the timezones
manually override the default column type generated by Hibernate's schema export from timestamp with time zone to timestamp, using the JPA @Column annotation
do not set hibernate.type.preferred_instant_jdbc_type, nor use @JdbcTypeCode(TIMESTAMP), so that Hibernate doesn't know you're using a local timestamp as your column type
finally, and most importantly, use setParameter("timestamp", Timestamp.from(instant)) when binding the parameter of the query, instead of just passing the Instant object

FTR, the test code I have used is the following:

@DomainModel(annotatedClasses = UTCNormalizedInstantTest.Instantaneous.class)
@SessionFactory
@ServiceRegistry(settings = {@Setting(name = AvailableSettings.TIMEZONE_DEFAULT_STORAGE, value = "NORMALIZE_UTC"),
//      @Setting(name = AvailableSettings.JDBC_TIME_ZONE, value="UTC"),
//      @Setting(name = AvailableSettings.PREFERRED_INSTANT_JDBC_TYPE, value="TIMESTAMP")
})
public class UTCNormalizedInstantTest {

    @Test
    void test(SessionFactoryScope scope) {
        TimeZone.setDefault(TimeZone.getTimeZone("EST"));
        Instant timestamp = Instant.parse("2022-12-31T23:00:00Z");
        UUID id = scope.fromTransaction( s-> {
            Instantaneous z = new Instantaneous();
            z.instant = timestamp;
            s.persist(z);
            return z.id;
        });
        scope.inSession( s-> {
            Instantaneous z = s.find(Instantaneous.class, id);
            assertEquals( timestamp, z.instant );
        });
        scope.inSession( s-> {
            Instant i = s.createQuery("select i.instant from Instantaneous i", Instant.class)
                    .getSingleResult();
            assertEquals( timestamp, i );
            Instantaneous a = s.createQuery("from Instantaneous i where i.instant=:timestamp", Instantaneous.class)
                    .setParameter("timestamp", timestamp )
                    .getSingleResult();
            assertEquals( timestamp, a.instant );
            Instantaneous b = s.createQuery("from Instantaneous i where cast(i.instant as Timestamp)=:timestamp", Instantaneous.class)
                    .setParameter("timestamp", Timestamp.from(timestamp) )
                    .getSingleResult();
            assertEquals( timestamp, b.instant );
        });
    }

    @Entity(name = "Instantaneous")
    public static class Instantaneous {
        @Id
        @GeneratedValue
        UUID id;
//      @JdbcTypeCode(Types.TIMESTAMP)
        @Column(columnDefinition = "timestamp")
        Instant instant;
    }
}

Now, uncommenting any one of the three commented lines, or removing the @Column annotation makes this test pass.

So the solution is, either:

set hibernate.type.preferred_instant_jdbc_type=TIMESTAMP,
annotate the field @JdbcTypeCode(TIMESTAMP), or
set hibernate.jdbc.time_zone to the server time zone (you say you tried this but I'm willing to bet that if you debug you'll find it didn't stick).

Note that I was only able to eventually reproduce the problem by using setParameter("timestamp", Timestamp.from(instant)). It does not appear to occur if I use setParameter("timestamp", instant), which of course better and more natural. This makes sense. I suppose (but do not know) that if you debug the code generated by Spring, you will find that it is (incorrectly) converting your Instant to a Timestamp before passing it to setParameter().

FTR, the difference between H5 and H6 is that by default H6 uses a timestamp with time zone to store Instants, rather than a timestamp, since a timestamp with no time zone is more like a Java LocalDateTime. Since you're using timestamp to store your Instant, you need to let Hibernate know about that, or, at the very least, let it know about the mismatch between the JVM and Oracle time zones.

CodePudding user response：

Hibernate 5 was using following method to perform all date/time related calculations:

@Override
protected X doExtract(ResultSet rs, String name, WrapperOptions options) throws SQLException {
  return options.getJdbcTimeZone() != null ?
   javaTypeDescriptor.wrap( rs.getTimestamp( name, Calendar.getInstance( options.getJdbcTimeZone() ) ), options ) :
   javaTypeDescriptor.wrap( rs.getTimestamp( name ), options );
}

Now, for your "Instant case", Hibernate 6 uses following method:

protected void doBind(
        PreparedStatement st,
        X value,
        int index,
        WrapperOptions wrapperOptions) throws SQLException {
    final OffsetDateTime dateTime = javaType.unwrap( value, OffsetDateTime.class, wrapperOptions );
    // supposed to be supported in JDBC 4.2
    st.setObject( index, dateTime.withOffsetSameInstant( ZoneOffset.UTC ), Types.TIMESTAMP_WITH_TIMEZONE );
}

which actually looks weird because HBN binds timezone-aware data-type to non-timezone-aware column and the question is: what does the expression st.setObject(index, odt, 2014) really mean and what actual value will DB engine use when selecting rows?

Answer:

JDBC driver sends "expected" value
DB engine adjusts both date/time data stored in table and query parameters to UTC timezone (for stored data it uses session timezone as a baseline - ALTER SESSION SET TIME_ZONE='UTC' solve the issue as well as setting JDK timezone to UTC) prior doing any comparisons (that is the reason why your select statement does not return data) - that actually becomes clear if we take a look at execution plan:

explain plan for
select * from entity
where "demo_timestamp" = to_timestamp_tz(:1);

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'basic  predicate'));

Explained.


PLAN_TABLE_OUTPUT                                           
------------------------------------------------------------
Plan hash value: 92196100
 
------------------------------------
| Id  | Operation         | Name   |
------------------------------------
|   0 | SELECT STATEMENT  |        |
|*  1 |  TABLE ACCESS FULL| ENTITY |
------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT                                           
------------------------------------------------------------
 
   1 - filter(SYS_EXTRACT_UTC(INTERNAL_FUNCTION("demo_timest
amp"))=SYS_EXTRACT_UTC(TO_TIMESTAMP_TZ(:1)))

14 rows selected.

So, yes, there are "breaking changes" (or a bug) in Hibernate 6 and now storing timezone-aware data in non-timezone-aware columns may cause a plenty of issues (wrong results, slow queries, etc)