Range<ZonedDateTime> produces incorrect results (PostgreSQL)

[lbruun]

Describe the bug

Range<ZonedDateTime> is no longer the same after having been persisted and then retrieved. This produces incorrect is-in-range? results.

This is not so much the library's fault as it is a consequence of how PostgreSQL timestamps travel the wire and are interpreted by the JDBC driver.

To Reproduce

Consider this:

ZonedDateTime zdt = ZonedDateTime.parse("2007-12-03T09:00:00Z");
Range<ZonedDateTime> zdtr = Range.closedInfinite(zdt);

This produces a range like this ['2007-12-03T09:00:00Z', INFINITY).

And we have zdtr.contains(zdt) => true (as expected).

Now save the Range into the database (in a column of type tztsrange, of course) and retrieve it again into new variable, let's call it zdtrRetrieved. Now the Range looks like this:

zdtrRetrieved --> ['2007-12-03T10:00:00+01', INFINITY). (depending on your session's timezone, mine is UTC+1).

This shouldn't be a problem, right? The zdtr and zdtrRetrieved are functionally the same, one would think.

Not so: now you have zdtrRetrieved.contains(zdt) --> false .. which is certainly unexpected.

THIS BREAKS MOST - IF NOT ALL - USAGE OF THIS TYPE, IMO.

Expected behavior

Persisting a Range<ZonedDateTime> and retrieving it again should ideally yield the same value. However, this is likely not possible to accomplish with the way JDBC and PostgreSQL works.

Possible fix

As a workaround: Part of the problem is how ZonedDateTime.compareTo() works, but it is intentional from the designers of the JDK. For this reason, they have added methods isBefore(), isAfter() and isEqual() which I believe should be used in lieu of compareTo(). This will produce the expected result when doing is-in-range? testing.

Note: One cannot argue that for any usage of ZoneDateTime that isBefore()/isAfter()/isEqual() should always be used in lieu of compareTo(). However, in this case we know PostgreSQL will never store a Zone Id (it won't even store a Zone Offset, it just fakes it), so for all practical purposes what goes on here is a timestamp with an offset.

Btw: It doesn't help to change the library's implementation to do (PostgreSQL) tztsrange <--> (Java) Range<OffsetDateTime> as that one would suffer the exact same problem. However, an implementation using Range<Instant> would solve the problem. If this is not desirable, then the solution where isBefore()/isAfter()/isEqual() is used for comparison instead of compareTo().

[vladmihalcea]

If you think that Range<Instant> is a good alternative, then you should definitely implement this feature.

Send me a Pull Request when the feature is ready.

[vladmihalcea]

Close as no Pull Request was provided for this feature.