Question 212468 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – JPA adjust Postgres timestamp without timezone to server timezone

I meet some JPA behavior I cannot understand.

  1. I’m saving specific dateTime ex. 2023-01-01T12:00:00Z (this is Instant) to Postgresql Timestamp without timezone.
  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 db this column has no timezone indicator. Why JPA adjust this time?

Thanks for any ideas

Tags:

2

Answers


  1. 0

    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 then 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 stores 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.

    Login or Signup to reply.
  2. 0

    Wrong column type in your table

    I’m saving specific dateTime ex. 2023-01-01T12:00:00Z (this is Instant) to Postgresql Timestamp without timezone.

    You have chosen the wrong data type for your column. The type TIMESTAMP WITHOUT TIME ZONE cannot represent a moment, a specific point on the timeline. That type stores only a date with time-of-day, such as noon on Jan 23rd next year. But we have know way to know if that was meant to be noon in Tokyo, noon in Toulouse, or noon in Toledo.

    The Z at the end of your input string means an offset of zero hours-minutes-seconds from UTC. That, combined with a date and time, determines a moment.

    To store that moment you need to define your column as TIMESTAMP WITH TIME ZONE.

    This has been covered many many times already on Stack Overflow, with many existing Answers including some written by me. Search to learn more such as using the Java type LocalDateTime with one column type and OffsetDateTime with the other column type.

    If you use the correct type, all your problems will vanish. And the data stored in your database will be meaningful rather than ambiguous.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search