I did some research, and it turns out there is actually a problem in the JDK, but it will never get fixed: https://bugs.openjdk.java.net/browse/JDK-8061577
The problem is a fundamental difference in the internal calendar used by GregorianCalendar and java.time. In short, they are both right, but they make different assumptions, and are thus not always compatible.

I updated my PR to test for this specific problem and to implement a workaround for LocalDateTime, Instant, ZonedDateTime and OffsetDateTime. LocalDate doesn't seem affected, but I added a test to demonstrate that.
The resulting behavior probably won't be perfect, because there's a fundamental mismatch between java.sql.Timestamp (used by the JDBC drivers) and the java.time types, but it should be a bit better. For a perfect match between the database and your application, I can't think of any other solution than using java.sql.Timestamp in your application, unfortunately.

Regarding the other types that unwrap/wrap are supposed to handle: the root cause is not the same as the problem you detected in LocalDateTime, so it would deserve a separate ticket. I tried to expand a bit on your tests and started to experiment with some fixes, but there are lots of unexpected results. Some are obviously wrong (like returning an Instant when a Long is requested), while others are subjective (different people will want to convert OffsetDateTime to java.sql.Date differently, for example). I doubt everything can be fixed.

However, as far as I can see, these other conversions are not used in Hibernate ORM unless you define your own custom types that map OffsetDateTime (for example) to something other than java.sql.Timestamp.

In case it may be of use, here's the branch where I experimented: https://github.com/yrodiere/hibernate-orm/tree/java-type-descriptor-bugs