spring.jpa.properties.hibernate.jdbc.time_zone applied on writes but not on reads?

I’m using:

• spring boot 2.0.4.RELEASE
• spring-data-jpa 2.0.9.RELEASE
• hibernate-core 5.2.17.Final
• hibernate-jpa-2.1-api 1.0.0.Final
• postgres jdbc driver 42.2.9

I have the following entity:

@Entity
@EntityListeners(AuditingEntityListener.class)
public class MyEntity implements Serializable
{
    @Column(nullable = false, updatable = false)
    @CreatedDate
    private LocalDateTime createdDate;

    @Column(nullable = false)
    @LastModifiedDate
    private LocalDateTime lastModifiedDate;

    public LocalDateTime getCreatedDate()
    {
        return createdDate;
    }

    public LocalDateTime getLastModifiedDate()
    {
        return lastModifiedDate;
    }
}

and the following property set in application.yaml:

spring:
  jpa:
    properties:
      hibernate:
        jdbc:
          time_zone: UTC

Regardless of what the JVM timezone/default timezone is, I want to save and return timestamps in UTC.

For testing purposes, I have set the timezone of my application code to US/Hawaii:

TimeZone.setDefault(TimeZone.getTimeZone("US/Hawaii"));

When I save an entity, it is correctly written to the database with a UTC timestamp:

[16:43:04.636Z #4c5.042 TRACE -            -   ] o.h.t.d.sql.BasicBinder: binding parameter [1] as [TIMESTAMP] - [2020-03-02T06:43:04.581]
[16:43:04.645Z #4c5.042 TRACE -            -   ] o.h.t.d.sql.BasicBinder: binding parameter [2] as [TIMESTAMP] - [2020-03-02T06:43:04.581]
[16:43:04.649Z #4c5.042 TRACE -            -   ] o.h.r.j.i.ResourceRegistryStandardImpl: Closing prepared statement [HikariProxyPreparedStatement@336047848 wrapping insert into myentity (createdDate, lastModifiedDate) values ('2020-03-02 16:43:04.581+00', '2020-03-02 16:43:04.581+00')]

However, when I read it back again, it’s coming back as the default timezone I’ve set in my application code: US/Hawaii, not UTC:

[16:43:04.692Z #4c5.043 TRACE -            -   ] o.h.t.d.sql.BasicExtractor: extracted value ([createdD4_0_0_] : [TIMESTAMP]) - [2020-03-02T06:43:04.581]
[16:43:04.692Z #4c5.043 TRACE -            -   ] o.h.t.d.sql.BasicExtractor: extracted value ([lastModi5_0_0_] : [TIMESTAMP]) - [2020-03-02T06:43:04.581]
[16:43:04.695Z #4c5.043 TRACE -            -   ] o.h.l.p.e.p.i.ResultSetProcessorImpl: Done processing result set (1 rows)
[16:43:04.696Z #4c5.043 TRACE -            -   ] o.h.l.p.e.p.i.AbstractRowReader: Total objects hydrated: 1
[16:43:04.696Z #4c5.043 TRACE -            -   ] o.h.l.p.e.p.i.ResultSetProcessingContextImpl: Skipping create subselects because there are fewer than 2 results, so query by key is more efficient.
[16:43:04.696Z #4c5.043 TRACE -            -   ] o.h.r.j.i.ResourceRegistryStandardImpl: Releasing result set [HikariProxyResultSet@622126582 wrapping org.postgresql.jdbc.PgResultSet@3f0764b8]
[16:43:04.696Z #4c5.043 TRACE -            -   ] o.h.r.j.i.ResourceRegistryStandardImpl: Closing result set [HikariProxyResultSet@622126582 wrapping org.postgresql.jdbc.PgResultSet@3f0764b8]
[16:43:04.696Z #4c5.043 TRACE -            -   ] o.h.r.j.i.ResourceRegistryStandardImpl: Releasing statement [HikariProxyPreparedStatement@1612081040 wrapping select myentity0_.createdDate as createdD4_0_0_, myentity0_.lastModifiedDate as lastModi5_0_0_, where myentity0_.id='123']

I have tried adding serverTimezone=UTC&useLegacyDatetimeCode=false to my JDBC URL but it made no difference.

Maybe related: https://hibernate.atlassian.net/browse/HHH-13417

Any help is much appreciated.

Update

Based on @midhun mathew’s answer, I found it was enough to take control of setting the dates in application code to resolve this (removing the time_zone property from application.yaml as well):

myEntity.setCreatedDate(LocalDateTime.ofInstant(Instant.now(), ZoneOffset.UTC))

public void setCreatedDate(LocalDateTime createdAt) 
{ 
  this.createdAt = createdAt; 
}

Now, when writing to the DB, the dates are ‘bound’ and inserted as UTC (compared with the original post in which the dates were ‘bound’ as USHawaii, but inserted as UTC):

[10:10:21.475Z #065.042 TRACE -            -   ] o.h.t.d.sql.BasicBinder: binding parameter [1] as [TIMESTAMP] - [2020-03-03T10:10:17.400]
[10:10:21.476Z #065.042 TRACE -            -   ] o.h.t.d.sql.BasicBinder: binding parameter [2] as [TIMESTAMP] - [2020-03-03T10:10:17.400]
[HikariProxyPreparedStatement@860888944 wrapping insert into myentity(createdDate, lastModifiedDate) values ('2020-03-03 10:10:17.4-10', '2020-03-03 10:10:17.4-10')]
[10:10:21.479Z #065.042 TRACE -            -   ] 

And when reading the entity from the db, the dates are no longer read as US/Hawaii, but UTC:

[10:10:24.527Z #065.043 TRACE -            -   ] o.h.t.d.sql.BasicExtractor: extracted value ([createdD4_0_0_] : [TIMESTAMP]) - [2020-03-03T10:10:17.400]
[10:10:24.527Z #065.043 TRACE -            -   ] o.h.t.d.sql.BasicExtractor: extracted value ([lastModi5_0_0_] : [TIMESTAMP]) - [2020-03-03T10:10:17.400]

Answer

I had faced the same issue. My database timezone was in UTC and my application timezone was in singapore. I solved this problem by having both the Entity and the table to have date in UTC so that there will need to be no conversion between them. Then I did the conversions between timestamps in code in the getters and setters.

So your MyEntity class will store the createdAt and lastModifiedAt in UTC.

In the setter you can have something like

public void setCreatedDate(LocalDateTime createdAt)
{         
    this.createdAt = createdAt.atZone(ZoneId.systemDefault()).withZoneSameInstant(ZoneOffset.UTC).toLocalDateTime();
}

In the getter you can have something like

public LocalDateTime getCreatedDate()
{         
    return createdAt.atZone(ZoneId.systemDefault()).withZoneSameInstant(ZoneOffset.UTC).toLocalDateTime();
}

You might also have to remove the time zone property and the @CreatedDate and @LastModifiedDate annotations as this was converting the time.