Timestamps before epoch with micros/nanos part are shifted by 1s #336
Description
For timestamps using micros/nanos unit (TIMESTAMP, TIMESTAMP_NS, TIMESTAMP_WITH_TIME_ZONE), the value on Java side will be shifted by 1s for dates before epoch that have actual micros/nanos part.
For example, running the following query:
SELECT TIMESTAMPTZ '1969-01-01 00:00:00.1Z';
Returns:
1969-01-01 00:00:01.100+00
Looks like the issue is in DuckDBTimestamp#createInstant:
createInstant(-1_000_001, ChronoUnit.MICROS) returns 1969-12-31T23:59:59.999999Z instant (instead of 1969-12-31T23:59:58.999999Z).
This method use nanosPartMicros/nanosPartNanos to get the nanos part without using corresponding micros2seconds/nanos2seconds to get the seconds part. As nanosPart* methods have special handling to keep the nanos adjustment positive (event for negative timestamps), the corresponding *2seconds methods must be used to account for the 1s offset for negative timestamps.
Alternatively, as Instant#ofEpochSecond allows an arbitrary number of nanoseconds (even negative), the nanos part could be obtained directly using value % 1_000_000 instead of using nanosPartMicros (and respectively for nanos unit case). It looks like the nanosPartMicros/nanosPartNanos methods have been created for use with LocalDateTime#ofEpochSecond that does restrict nanos part to positive 0 to 999_999_999 range.