ZonedDateTime with incorrect timezone fetched from database

Question

I have an entity called "Turno" that has an attribute ZonedDateTime.

   @Column(name = "fecha", nullable = false)
    @Temporal(TemporalType.TIMESTAMP)
    private ZonedDateTime fechaTurno;

When saving this entity, I have no problem because the value and time zone are stored correctly in PostgreSQL. The problem is that when retrieving rows from the backend, the time zone is converted to UTC.

Here you can see how the date is stored (correctly)

And then, when I fetch this rows the timezone is "UTC"

I already configured my project with these lines

Application.properties

spring.jackson.time-zone=America/Argentina/Buenos_Aires
spring.jpa.properties.hibernate.jdbc.time_zone=America/Argentina/Buenos_Aires

main.java

@PostConstruct
public void init(){
    TimeZone.setDefault(TimeZone.getTimeZone("America/Argentina/Buenos_Aires"));
}

I need to retrieve the date with the correct time zone (GMT-03). Or I have to explicitly modify every time I fetch this date

Answer 1

tl;dr

entity called "Turno" that has an attribute ZonedDateTime.

Wrong class. Use OffsetDateTime for TIMESTAMP WITH TIME ZONE SQL type.

Example data is sensible

Regarding your example data, 2024-05-27 09:00:00-03 is the same moment as 2024-05-27T12:00Z. The -03 means three hours behind UTC. The Z is short for “offset from UTC of zero hours-minutes-seconds”. At that moment, the Argentina/Buenos_Aires was using an offset of three hours before UTC. So 9 AM in that zone is the same as noon in UTC, offset by negative three hours.

In other words, 2024-05-27 09:00:00-03 & 2024-05-27T12:00Z are two ways of looking at one single point on the timeline.

JDBC maps to OffsetDateTime

ZonedDateTime is not mapped to a SQL type in JDBC.

That lack is because the SQL standard recognizes only offset-from-UTC, not time zone. The types defined in the standard, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIME ZONE are misnomers; the standard authors meant offset-from-UTC. (Just one of many quirks is the SQL standard.)

Offset versus Zone

What is the difference between offset and zone? An offset is merely a number of hours-minutes-seconds ahead or behind the temporal meridian of UTC. A time zone is much more. A time zone is a named history of the past, present, and future changes to the offset used by the people of a particular region as decided by their politicians.

For exchanging with a database column of a type akin to 👉🏽 the SQL standard type TIMESTAMP WITH TIME ZONE use only OffsetDateTime class in Java.

In straight JDBC:

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

… and:

myPreparedStatement.setObject( … , odt ) ;

Apply your desired time zone.

ZoneId z = ZoneId.of( "xx" ) ;
ZonedDateTime zdt = odt.at

Postgres stores in UTC (offset of zero)

Furthermore, understand that Postgres always stores TIMESTAMP WITH TIME ZONE values with an offset from UTC of zero hours-minutes-seconds — always. Any offset or zone supplied with an input is used to adjust the date & time to UTC. Any value retrieved is always in UTC — always.

After using any supplied offset or zone to adjust to UTC, Postgres discards that offset/zone. All that remains is a date and time as seen from an offset of zero.

So this is yet another reason you cannot use ZonedDateTime with your TIMESTAMP WITH TIME ZONE column: There is 👉🏽 no known time zone to be applied to the retrieved UTC value.

Spring

Apparently Spring can dynamically apply a specific time zone to the UTC value retrieved from Postgres. I do not really know, as I do not use Spring. But that is what I gather from the info you provided.

Personally, I would rather retrieve the exact value from the database, then massage the data afterwards. But reasonable people may disagree.

Beware middleware

You said:

Here you can see how the date is stored (correctly)

No, not correct. If what you show in that screenshot is from a database tool such as pgAdmin, that tool is lying to you.

Postgres stores TIMESTAMP WITH TIME ZONE values only with an offset of zero. So the displayed offset of three hours (-03) behind UTC is not really true.

Unfortunately, some tools and middleware choose to dynamically apply some default time zone to the TIMESTAMP WITH TIME ZONE value stored in UTC. This anti-feature creates the illusion of a time zone having been stored.

When using such tools with this misguided anti-feature, I suggest setting its default time zone to UTC for an offset of zero. That may be a session setting, but depends on the tool.