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

Question

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 US\Hawaii, 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]

score 3 · Accepted Answer · answered Mar 02 '20 at 18:24

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.

Thanks @midhun mathew, I've updated my post with what I needed to do - I found the conversions were not necessary in my case, if I always set the dates to UTC in application code. — rmf, Mar 03 '20 at 10:47
I think it only a workaround. Then we can say that hibernate property is not working as expected — user725455, Nov 10 '20 at 14:59

score 3 · Answer 2 · answered Oct 12 '21 at 15:38

Although @midhun showed you a proper way how to convert Instant to LocalDateTime, I'll try to give an understanding of what is going on so other people may not do the same mistake.

The answer to your question is no, it works for both. See documentation: https://docs.jboss.org/hibernate/orm/5.3/userguide/html_single/Hibernate_User_Guide.html#basic-datetime-time-zone

Regarding your issue, you give no information about your database (vendor and time zone) and schema, so it's hard to give a more precise answer. But I have a feeling that you are confusing all date types involved.

From the database perspective, every database handles dates in a different way. Postgres, for instance, always save dates (timestamptz) in UTC, then when reading, converts the date to the server timezone. This is what the documentation says: https://www.postgresql.org/docs/9.1/datatype-datetime.html

For timestamp with time zone, the internally stored value is always in UTC. 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.

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.3).

With that said, you have LocalDateTime in your Java code and I'll assume that you have TIMESTAMP with time zone in your database because of your attempt to set it to UTC and that the database is in US/Hawaii time zone.

The problem is that you are using incompatible date types - LocalDateTime has no time zone information - and the application and database were set to different timezones, and it was not handled properly! This is what happens:

Writing Local -> Time zoned
The local date is transformed by the application to a time zoned date by using the default time zone.

2020-03-03T10:10:17.400 -> 2020-03-03T16:10:17.400+0 first case (UTC)
2020-03-03T10:10:17.400 -> 2020-03-03T10:10:17.400-10 second case (US/Hawaii)

The converted date is what you see in the log:

insert into myentity (createdDate, lastModifiedDate) values ('2020-03-02 16:43:04.581+00', '2020-03-02 16:43:04.581+00')

insert into myentity(createdDate, lastModifiedDate) values ('2020-03-03 10:10:17.4-10', '2020-03-03 10:10:17.4-10')

Reading Time zoned -> Local
The time zoned date is transformed to Local by applying the default database time zone (the conversation is done in the database, not in the application)

2020-03-03T16:10:17.400+0 -> 2020-03-03T06:10:17.400 first case (US/Hawaii)
2020-03-03T10:10:17.400-10 -> 2020-03-03T10:10:17.400 second case (US/Hawaii)

You see the database converted values in the logs:

extracted value ([createdD4_0_0_] : [TIMESTAMP]) - [2020-03-02T06:43:04.581]

extracted value ([createdD4_0_0_] : [TIMESTAMP]) - [2020-03-03T10:10:17.400]

Remember that your database is in US/Hawaii zone. As the zone information is discarded by the database, no further conversion is done.

When possible, set the application and the database to the same time zone. Also, use compatible data types. For timestamptz, use Instant.

`Instant` is not supported by the PostreSQL JDBC driver. For `TIMESTAMP WITH TIME ZONE` it is better to use `OffsetDateTime`. https://jdbc.postgresql.org/documentation/head/java8-date-time.html — egelev, Jun 16 '22 at 13:33
@egelev I have been using Java 11 + Spring JPA + Hibernate + Postgres + Instant for over 2 years with success. So there is no issue with this setup and all needed conversion is done transparently. Also, if you read the link you sent, in the same paragraph, it states that "Also note that all OffsetDateTime instances will have be in UTC" and I'm pretty sure you don't follow that rule ;) — Jordan Silva, Jun 17 '22 at 14:25

score 0 · Answer 3 · edited Jun 09 '21 at 21:20

0

This should make it work correctly:

TimeZone.setDefault(TimeZone.getTimeZone("UTC"));

edited Jun 09 '21 at 21:20

csalmhof

1,820
2
15
24

answered Jun 09 '21 at 16:09

Mike Packer

63
5

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

3 Answers3

Linked