Hi…
So I have a question regarding my project GitHub - agreedSkiing/hibernate-timezone-conversion-reproducer, I am experiencing a time-zone conversion bug/issue in Hibernate ORM 5, 6 and Reactive when my applications runs in the GMT+1 time-zone when the database connection does not have its connection time-zone specified (jdbc.timezone).
I know how to fix this issue and that is to set the database drivers time-zone to UTC since I’m using postgres but I think this issue is very odd since Hibernate ORM 6 removes one the time-zone conversion issues () that Hibernate ORM 5 has and Hibernate Reactive instead produces more failures ().
Random endnotes: I do have some random notes in the readme file, but those were written before this post and I started thinking this was a Quarkus only issue but then found out that Spring Boot also had I just had to try with the basic hibernate implementation.
Also sorry in advance since this topic is also contains Hibernate Reactive.
Time zone stuff is hard and requires a lot of thought which is time consuming. To make things easier, we usually recommend that the app and the DB should run in the UTC time zone. Note that you can configure that for the JVM by setting the system property user.timezone=UTC i.e. pass -Duser.timezone=UTC to the arguments of your app.
If you can’t do that for some reason, you can use the hibernate.jdbc.time_zone property instead. The effect of that is, that Timestamp values (which are in UTC), are converted to the specified time zone when sent to the database.
Unless you use the timestamptz type in PostgreSQL, the database stores “local timestamps”. Local means that the stored timestamps are relative to the time zone configured on the DB. The timestamptz type on the other hand stores values in the UTC time zone.
As far as I understand your scenario, the problems you are facing can be solved by configuring the hibernate.jdbc.time_zone to match the time zone you configured on the DB, which is UTC.
Right my rambling got the better of me with the first post, this it’s not really a problem since I already know about the fix with the hibernate.jdbc.time_zone or setting the JVM to run with user.timezone=UTC, but since we are using timestampz in postgres for a new project I started to look into this issue more and started wondering if the problems we where facing when trying out different Hibernate implementations is a bug or not. What I gather from your post is that this is an expected behaviour and we should always ensure that either our JVM runs in UTC or the connection to the database has the correct time zone specified.
Just for completeness, with Hibernate 6, you can use java.time.Instant in your model and it will use the timestamptz on PostgreSQL behind the scenes. There is no time zone configurations necessary when using just Instant
Either how as I understand time zones are difficult to handle and this is not a bug, and the solution to handle this the best is either setting the jdbc connections time zone with the help of hibernate.jdbt.time_zone or by setting it in the JVM with -Duser.timezone=UTC or in the java application with TimeZone.setDefault(TimeZone.getTimeZone("UTC")) or any other way that specifies the java applications time zone.
) that Hibernate ORM 5 has and Hibernate Reactive instead produces more failures (
).