JPA adjust Postgres timestamp without timezone to server timezone

Question

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

Answer 1

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.

Answer 2

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 no way to know if that was meant to be noon in Tokyo, noon in Toulouse, or noon in Toledo — three very different moments, several hours apart.

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.

Writing:

OffsetDateTime odt = myInstant.atOffset( ZoneOffset.UTC ) ;
myPreparedStatement.setObject( … , odt ) ;

Reading:

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;  // Accessing a column of type TIMESTAMP WITH TIME ZONE. 
Instant instant = odt.toInstant() ;

Your tools may lie to you

Understand that Postgres stores all TIMESTAMP WITH TIME ZONE values with an offset from UTC of zero hours-minutes-seconds. Any zone or offset info submitted with an input is used to adjust that input to UTC.

So, values retrieved from a TIMESTAMP WITH TIME ZONE column are always in UTC. However, middleware, drivers, and tooling may (unfortunately) choose to apply a zone or offset to retrieved values, thereby telling a lie.