postgresqljpatimestamptimezonejava.time.instant

JPA adjust Postgres timestamp without timezone to server timezone

I encountered some Jakarta Persistence (formerly JPA) behavior I cannot understand.

  1. I'm saving specific dateTime ex. 2023-01-01T12:00:00Z (this is Instant) to PostgreSQL TIMESTAMP WITHOUT TIME ZONE.
  2. I'm reading this value and receive the same date: 2023-01-01T12:00:00Z which is expected.
  3. I change server timezone from +0 to +1 and now, when I start application, it reads from db: 2023-01-01T11:00:00 despite, in database this column has no timezone indicator.

Why does Jakarta Persistence adjust this time?

Thanks for any ideas

Solution

  • As you configure DB will not store any timezone for that date time , you can think it just store it as a local date time. It is up to the client application (i.e JDBC driver in your case) to define its timezone when fetching it.

    So if you configure the JDBC driver to have different timezones when storing a date time and fetching the same date time , such time shifting behaviour will happen.

    The timezone of the JDBC driver is basically depending by the following priority . If you do not explicitly configure the timezone of the high priority items , it will then default to the lower priority items.

    1. Timezone in JDBC connection url
    2. JVM timezone
    3. OS timezone

    Hibernate even provide a way to configure the timezone which have even higher priority than JDBC connection url through the property hibernate.jdbc.time_zone (see this for detail)

    So I believe your case can be explained by the followings. Even though you store 2023-01-01T12:00:00 UTC :

    1. DB will store it as local date time only , that is 2023-01-01 12:00:00
    2. Since you change the timezone of the app server to UTC+1 , your Java app interprets it as 2023-01-01 12:00:00 UTC+1 when fetching it from DB.
    3. Since Java Instant is represented based on UTC timezone , you then see it as 2023-01-01 11:00:00 UTC (Note : 2023-01-01 12:00:00 UTC+1 = 2023-01-01 11:00:00 UTC )

    So if you are using hibernate , I would suggest to configure hibernate.jdbc.time_zone to UTC which aligns with the expected timezone of the date time storing in DB. It makes your app have more deterministic timezone behaviour which will not affected by the timezone setting of JVM or server 's OS.

    By the way , it is more about the JDBC driver that adjust the time but not JPA. You should get the same behaviour even you do not use JPA.