Exasol is transforming old dates incorrectly:
SELECT ADD_SECONDS('1970-01-01 00:00:00',-30610224000.000)
-- 0999-12-27 00:00:00
SELECT ADD_SECONDS('1970-01-01 00:00:00',-30609792000.000)
-- 1000-01-01 00:00:00
While in java:
System.out.println(Instant.ofEpochMilli(0).plus(-30610224000L, ChronoUnit.SECONDS));
System.out.println(Instant.ofEpochMilli(0).plus(-30609792000L, ChronoUnit.SECONDS));
1000-01-01T00:00:00Z
1000-01-06T00:00:00Z
Do you know why that diference?
Not knowing Exasol I bet it’s the difference between the Julian and the proleptic Gregorian calendar.
The Julian calendar (named after Julius Caesar) has leap years every 4 years always. At a point in history they discovered that this gave a bit too many leap years. So under pope Gregor the Gregorian calendar was introduced, leaving out leap years for years that are divisible by 100 but not by 400 (so 1900 was not a leap year, 2000 was and 2100 will not be). The change is known as the Gregorian change or changeover.
The proleptic Gregorian calendar is a newer invention. It extrapolates the Gregorian calendar back into times before the Gregorian change, thus using dates that are in disagreement with the calendar actually used back in those times. The advantages being that calculations are simpler, and we are free from deciding when to make the Gregorian change, which is nice since each jurisdiction had their own date for that.
Instant and the other classes from java.time use the proleptic Gregorian calendar, so give inaccurate dates for years 999 and 1000. If Exasol uses the Julian calendar (which I do not know), this could be the explanation for the differences you observed.
Links