Why is java.sql’s Timestamp.of() converting incorrectly from java.time’s LocalDateTime?

I ran into a bug where java.sql's

Timestamp.valueOf(LocalDateTime.MIN())

is converting incorrectly.

Given java.time's

 LocalDateTime.MIN() = -999999999-01-01T00:00:00

But when I try to convert it into an SQL friendly TimeStamp, why do I run into a differently parsed time which is incorrectly Skewed?

Timestamp.valueOf(LocalDateTime.MIN()) = +169087565-03-15 04:51:43.000000

I was expecting the oldest date possible, but this gives out a MAX.

PS:

I worked around it using

Timestamp.valueOf(//
        LocalDateTime.of(LocalDate.ofYearDay(1800,1), LocalTime.MIDNIGHT)

tl;dr

Never use Timestamp.

Avoid java.sql.Timestamp

You said:

SQL friendly TimeStamp

The java.sql.Timestamp class is one of the terribly flawed legacy date-time classes that were years ago supplanted by the modern java.time classes defined in JSR 310.

Use OffsetDateTime, not Timestamp

JDBC 4.2 and later requires every JDBC driver to support java.time. Specifically, use OffsetDateTime instead of Timestamp.

Java offers three classes to represent a moment: Instant, OffsetDateTime, and ZonedDateTime. But only OffsetDateTime is mapped to a SQL type in JDBC because of the limitations of the SQL standard.

LocalDateTime

The LocalDateTime class cannot represent a moment as it lacks the context of a time zone or offset from UTC. The LocalDateTime class represents simply a date with time-of-day, nothing more.

Both the legacy type Timestamp and the modern type OffsetDateTime represent a moment, a point on the timeline. So you should not mix LocalDateTime with these.

Avoid minimums & maximums

.MIN

Avoid using the extremes of minimum/maximum. Database systems vary in their limits. All that I am aware of have a much smaller range than does java.time.

If you need some kind of signal value to indicate “old”, use a more contemporary value. For example, consider using java.time.Instant.EPOCH, the first moment of 1970 as seen in UTC, 1970-01-01T00:00Z.

OffsetDateTime epoch = Instant.EPOCH.atOffset( ZoneOffset.UTC ) ;

I was expecting the oldest date possible, but this gives out a MAX.

Probably you are seeing the effects of a wraparound due to integer overflow.

The internal implementation of Timestamp uses a 32-bit integer count of milliseconds since the epoch reference of 1970-01-01T00:00Z, negative before, positive after. The java.time classes use a much larger range by way of two counts, a 64-bit integer count of whole seconds from that same epoch reference plus a fractional second kept as a count of nanoseconds.

But there really is no point to exploring this issue. There is no reason to ever use java.sql.Timestamp.

Conversion

If handed a Timestamp, immediately convert using the new conversion methods added to the old classes. Specifically, java.time.Instant, then assign an offset to get a SQL-friendly OffsetDateTime object.

Instant instant = myTimestamp.toInstant() ;  // A moment as seen in UTC, always UTC. 
OffsetDateTime odt = instant.atOffset( ZoneOffset.UTC ) ;

If you need a Timestamp object to interoperate with old code not yet updated to java.time, convert.

java.sql.Timestamp ts = Timestamp.from( odt.toInstant() ) ;