What Offset is used to unmarshal an OffsetDateTime from a Postgres timestampTZ?

Question

I did read timestamps-and-time-zones-in-postgresql and understood that a timestampTZ is stored as a UTC-timestamp with any timezone/offset converted away and lost.

So, when loading a JPA/Hibernate Entity with an OffsetDateTime-field, bound to such a timestampTZ field, where does the Offset come from? Is it always converted into the JDBC-Connection's Timezone?

This is a kind of information-truncation where we actually lose the original timezone-information, thus we are set back to whatever timezone the JDBC-connection is bound to and thus, we are required to store the timezone-information additionally if we'd needed that?

If all of the above holds, wouldn't it be much clearer/precise to use Instant instead of OffsetBigTime, which represents an UTC-point-in-time exactly like timestampTZ is doing? Then I would have to at least apply the "proper timezone" explicitly in code and not have it applied "magically" by some DB-connection...

It seems like you're right; essentially, the Java 8 Time API is duplicate functionality, so it would be clearer to handle it in your Java code (presuming your Java application "owns" the entire schema). — chrylis -cautiouslyoptimistic-

Basil Bourque Basil Bourque · Accepted Answer · 2020-01-30T07:51:08

when loading an JPA/Hibernate Entity with a OffsetDateTime-field, bound to such an timestampTZ field, where does the Offset come from

While I do not use JPA or Hibernate (I use straight JDBC), I would expect that you receive an OffsetDateTime where the offset is zero hours-minutes-seconds ahead/behind UTC. We might refer to this as “at UTC” for short.

You can see for yourself. Retrieve an OffsetDateTime object, and call toString. If the resulting text shows +00:00 or Z at the end, hat means an offset of zero.

we are required to store the timezone-information additionally if we'd needed that?

Yes, if you care about the time zone or offset submitted to the database, you must save that information in a second column yourself with your own extra programming. By default, Postgres uses the submitted zone or offset info to adjust into UTC, then discards that zone or offset info.

I expect most business apps do not care what the original zone or offset was. Keep in mind that the moment, the point on the timeline, is not changed. Only the wall-clock time appears different. Postgres is like a person in Iceland (where UTC is their year-round permanent time zone) receiving a call from someone in Tokyo or Montréal. If both persons look up at the clock on their walls, the person in Tokyo sees a time of day several hours ahead of the Postgres person in Iceland. The Montréal person sees a time of day on the clock hanging on heir own wall to be hours behind that of the Postgres person in Iceland.

When you retrieve that OffsetDateTime object with an offset of zero, you can easily adjust into any time zone you desire.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;
ZoneId z = ZoneId.of( "Asia/Tokyo" ) ;
ZonedDateTime zdt = odt.atZoneSameInstant( z ) ;

wouldn't it be much more clearer/precise to use Instant

Yes!!

For whatever reasons, the folks controlling the JDBC API made the odd choice in JDBC 4.2 to require support for OffsetDateTime but not require support for Instant or ZonedDateTime. These other two classes are used much more often in most apps I imagine. So the logic of their choice escapes me.

Your JDBC driver may support Instant or ZonedDateTime. Just try it and see. The JDBC 4.2 API does not forbid such support; the API makes no mention of those types. Therefore support of Instant or ZonedDateTime is optional.

Then I would have to at least apply the "proper timezone" explicitly in code and not have it applied "magically" by some db-connection...

If you are retrieving java.time objects through JDBC 4.2 compliant drivers, I would be very surprised to see them applying a zone or offset to retrieved values. I expect you will only receive OffsetDateTime objects with an offset of zero. But I do not recall his behavior being mandated in the specification one way or the other. So always test the behavior of your particular JDBC driver.

Beware that retrieving values as text, or using other middleware tools such as PgAdmin, may well inject some default zone or offset. While well-intentioned, I consider this an anti-feature, creating the illusion of a particular zone having been saved in the database when it was not in fact.

What Timezone is used to unmarshal an OffsetDateTime from a Postgres timestampTZ?

Firstly, know that an offset is merely a number of hours, minutes, and seconds ahead or behind the prime meridian. A time zone is much more. A time zone is a history of the past, present, and future changes to the offset used by the people of a particular region.

So your title’s wording is contradictory. There is no time zone involved with an OffsetDateTime, thus the name. For a time zone, use the ZonedDateTime class.

What Offset is used to unmarshal an OffsetDateTime from a Postgres timestampTZ?

1 Answers