Message-ID: <gh-pgjdbc-pgjdbc-3930-c3872426265@github.com>
From: "m-van-tilburg (@m-van-tilburg)" <noreply+m-van-tilburg@github.com>
To: "pgjdbc/pgjdbc" <noreply+pgjdbc-pgjdbc@github.com>
Date: Mon, 09 Feb 2026 15:29:10 +0000
Subject: Re: [pgjdbc/pgjdbc] issue #3930: Revert semantic calendar changes introduced with #3837
In-Reply-To: <gh-pgjdbc-pgjdbc-3930@github.com>
References: <gh-pgjdbc-pgjdbc-3930@github.com>
List-Id: <gh-pgjdbc-pgjdbc.github.com>
X-GitHub-Author-Login: m-van-tilburg
X-GitHub-Comment-Id: 3872426265
X-GitHub-Comment-Type: issue_comment
X-GitHub-Issue: 3930
X-GitHub-Repo: pgjdbc/pgjdbc
X-GitHub-Type: comment
X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/3930#issuecomment-3872426265
Content-Type: text/plain; charset=utf-8

> `0001-01-01` gets more tricky because of the representation PostgreSQL uses for pre-0-year dates (namely postfixing with ` BC` as in you have to insert the string `0044-03-15 BC` into the table in order to get back with a select the date of Julius Caesar's assasination). Note that for such dates the easiest way with JDBC is to handle them as strings on the Java side (it gets very tricky with `PreparedStatement`s).
> 
> I have run some more tests for completeness sake and it's much worse than I originally thought:
> 
> Detailed test results
> value	inserting via	reading via	result	expected?
> '1000-01-01'	`psql`	`psql`	1000-01-01	✅
> '1000-01-01'	`psql`	driver 42.7.8 with `resultSet.getString(...)`	1000-01-01	✅
> '1000-01-01'	`psql`	driver 42.7.8 with `resultSet.getDate(...)`	1000-01-01	✅
> '1000-01-01'	`psql`	driver 42.7.9 with `resultSet.getString(...)`	1000-01-01	✅
> '1000-01-01'	`psql`	driver 42.7.9 with `resultSet.getDate(...)`	0999-12-27	⛔️
> '1000-01-01'	driver 42.7.8 as string	`psql`	1000-01-01	✅
> '1000-01-01'	driver 42.7.8 as string	driver 42.7.8 with `resultSet.getString(...)`	1000-01-01	✅
> '1000-01-01'	driver 42.7.8 as string	driver 42.7.8 with `resultSet.getDate(...)`	1000-01-01	✅
> '1000-01-01'	driver 42.7.8 as string	driver 42.7.9 with `resultSet.getString(...)`	1000-01-01	✅
> '1000-01-01'	driver 42.7.8 as string	driver 42.7.9 with `resultSet.getDate(...)`	0999-12-27	⚠️
> '1000-01-01'	driver 42.7.9 as string	`psql`	1000-01-01	✅
> '1000-01-01'	driver 42.7.9 as string	driver 42.7.8 with `resultSet.getString(...)`	1000-01-01	✅
> '1000-01-01'	driver 42.7.9 as string	driver 42.7.8 with `resultSet.getDate(...)`	1000-01-01	✅
> '1000-01-01'	driver 42.7.9 as string	driver 42.7.9 with `resultSet.getString(...)`	1000-01-01	✅
> '1000-01-01'	driver 42.7.9 as string	driver 42.7.9 with `resultSet.getDate(...)`	0999-12-27	⛔
> '1000-01-01'	driver 42.7.8 as `sql.Date`	`psql`	1000-01-01	✅
> '1000-01-01'	driver 42.7.8 as `sql.Date`	driver 42.7.8 with `resultSet.getString(...)`	1000-01-01	✅
> '1000-01-01'	driver 42.7.8 as `sql.Date`	driver 42.7.8 with `resultSet.getDate(...)`	1000-01-01	✅
> '1000-01-01'	driver 42.7.8 as `sql.Date`	driver 42.7.9 with `resultSet.getString(...)`	1000-01-01	✅
> '1000-01-01'	driver 42.7.8 as `sql.Date`	driver 42.7.9 with `resultSet.getDate(...)`	0999-12-27	⚠️
> '1000-01-01'	driver 42.7.9 as `sql.Date`	`psql`	1000-01-06	⛔
> '1000-01-01'	driver 42.7.9 as `sql.Date`	driver 42.7.8 with `resultSet.getString(...)`	1000-01-06	⚠️
> '1000-01-01'	driver 42.7.9 as `sql.Date`	driver 42.7.8 with `resultSet.getDate(...)`	1000-01-06	⚠️
> '1000-01-01'	driver 42.7.9 as `sql.Date`	driver 42.7.9 with `resultSet.getString(...)`	1000-01-06	⛔
> '1000-01-01'	driver 42.7.9 as `sql.Date`	driver 42.7.9 with `resultSet.getDate(...)`	1000-01-01	✅
> '0001-01-01'	`psql`	`psql`	0001-01-01	✅
> '0001-01-01'	`psql`	driver 42.7.8 with `resultSet.getString(...)`	0001-01-01	✅
> '0001-01-01'	`psql`	driver 42.7.8 with `resultSet.getDate(...)`	0001-01-01	✅
> '0001-01-01'	`psql`	driver 42.7.9 with `resultSet.getString(...)`	0001-01-01	✅
> '0001-01-01'	`psql`	driver 42.7.9 with `resultSet.getDate(...)`	0001-01-03	⛔
> '0001-01-01'	driver 42.7.8 as string	`psql`	0001-01-01	✅
> '0001-01-01'	driver 42.7.8 as string	driver 42.7.8 with `resultSet.getString(...)`	0001-01-01	✅
> '0001-01-01'	driver 42.7.8 as string	driver 42.7.8 with `resultSet.getDate(...)`	0001-01-01	✅
> '0001-01-01'	driver 42.7.8 as string	driver 42.7.9 with `resultSet.getString(...)`	0001-01-01	✅
> '0001-01-01'	driver 42.7.8 as string	driver 42.7.9 with `resultSet.getDate(...)`	0001-01-03	⚠️
> '0001-01-01'	driver 42.7.9 as string	`psql`	0001-01-01	✅
> '0001-01-01'	driver 42.7.9 as string	driver 42.7.8 with `resultSet.getString(...)`	0001-01-01	✅
> '0001-01-01'	driver 42.7.9 as string	driver 42.7.8 with `resultSet.getDate(...)`	0001-01-01	✅
> '0001-01-01'	driver 42.7.9 as string	driver 42.7.9 with `resultSet.getString(...)`	0001-01-01	✅
> '0001-01-01'	driver 42.7.9 as string	driver 42.7.9 with `resultSet.getDate(...)`	0001-01-03	⛔
> '0001-01-01'	driver 42.7.8 as `sql.Date`	`psql`	0001-01-01	✅
> '0001-01-01'	driver 42.7.8 as `sql.Date`	driver 42.7.8 with `resultSet.getString(...)`	0001-01-01	✅
> '0001-01-01'	driver 42.7.8 as `sql.Date`	driver 42.7.8 with `resultSet.getDate(...)`	0001-01-01	✅
> '0001-01-01'	driver 42.7.8 as `sql.Date`	driver 42.7.9 with `resultSet.getString(...)`	0001-01-01	✅
> '0001-01-01'	driver 42.7.8 as `sql.Date`	driver 42.7.9 with `resultSet.getDate(...)`	0001-01-03	⚠️
> '0001-01-01'	driver 42.7.9 as `sql.Date`	`psql`	0001-12-30 BC	⛔
> '0001-01-01'	driver 42.7.9 as `sql.Date`	driver 42.7.8 with `resultSet.getString(...)`	0001-12-30 BC	⚠️
> '0001-01-01'	driver 42.7.9 as `sql.Date`	driver 42.7.8 with `resultSet.getDate(...)`	0111-12-30	⚠️
> '0001-01-01'	driver 42.7.9 as `sql.Date`	driver 42.7.9 with `resultSet.getString(...)`	0001-12-30 BC	⛔
> '0001-01-01'	driver 42.7.9 as `sql.Date`	driver 42.7.9 with `resultSet.getDate(...)`	0001-01-01	✅
> '0044-03-15'	`psql`	`psql`	0044-03-15	✅
> '0044-03-15'	`psql`	driver 42.7.8 with `resultSet.getString(...)`	0044-03-15	✅
> '0044-03-15'	`psql`	driver 42.7.8 with `resultSet.getDate(...)`	0044-03-15	✅
> '0044-03-15'	`psql`	driver 42.7.9 with `resultSet.getString(...)`	0044-03-15	✅
> '0044-03-15'	`psql`	driver 42.7.9 with `resultSet.getDate(...)`	0044-03-17	⛔
> '0044-03-15'	driver 42.7.8 as string	`psql`	0044-03-15	✅
> '0044-03-15'	driver 42.7.8 as string	driver 42.7.8 with `resultSet.getString(...)`	0044-03-15	✅
> '0044-03-15'	driver 42.7.8 as string	driver 42.7.8 with `resultSet.getDate(...)`	0044-03-15	✅
> '0044-03-15'	driver 42.7.8 as string	driver 42.7.9 with `resultSet.getString(...)`	0044-03-15	✅
> '0044-03-15'	driver 42.7.8 as string	driver 42.7.9 with `resultSet.getDate(...)`	0044-03-17	⚠️
> '0044-03-15'	driver 42.7.9 as string	`psql`	0044-03-15	✅
> '0044-03-15'	driver 42.7.9 as string	driver 42.7.8 with `resultSet.getString(...)`	0044-03-15	✅
> '0044-03-15'	driver 42.7.9 as string	driver 42.7.8 with `resultSet.getDate(...)`	0044-03-15	✅
> '0044-03-15'	driver 42.7.9 as string	driver 42.7.9 with `resultSet.getString(...)`	0044-03-15	✅
> '0044-03-15'	driver 42.7.9 as string	driver 42.7.9 with `resultSet.getDate(...)`	0044-03-17	⛔
> '0044-03-15'	driver 42.7.8 as `sql.Date`	`psql`	0044-03-15	✅
> '0044-03-15'	driver 42.7.8 as `sql.Date`	driver 42.7.8 with `resultSet.getString(...)`	0044-03-15	✅
> '0044-03-15'	driver 42.7.8 as `sql.Date`	driver 42.7.8 with `resultSet.getDate(...)`	0044-03-15	✅
> '0044-03-15'	driver 42.7.8 as `sql.Date`	driver 42.7.9 with `resultSet.getString(...)`	0044-03-15	✅
> '0044-03-15'	driver 42.7.8 as `sql.Date`	driver 42.7.9 with `resultSet.getDate(...)`	0044-03-17	⚠️
> '0044-03-15'	driver 42.7.9 as `sql.Date`	`psql`	0044-03-13	⛔
> '0044-03-15'	driver 42.7.9 as `sql.Date`	driver 42.7.8 with `resultSet.getString(...)`	0044-03-13	⚠️
> '0044-03-15'	driver 42.7.9 as `sql.Date`	driver 42.7.8 with `resultSet.getDate(...)`	0044-03-13	⚠️
> '0044-03-15'	driver 42.7.9 as `sql.Date`	driver 42.7.9 with `resultSet.getString(...)`	0044-03-13	⛔
> '0044-03-15'	driver 42.7.9 as `sql.Date`	driver 42.7.9 with `resultSet.getDate(...)`	0044-03-15	✅
> '0044-03-15 BC'	`psql`	`psql`	0044-03-15 BC	✅
> '0044-03-15 BC'	`psql`	driver 42.7.8 with `resultSet.getString(...)`	0044-03-15 BC	✅
> '0044-03-15 BC'	`psql`	driver 42.7.8 with `resultSet.getDate(...)`	0044-03-15	±
> '0044-03-15 BC'	`psql`	driver 42.7.9 with `resultSet.getString(...)`	0044-03-15 BC	✅
> '0044-03-15 BC'	`psql`	driver 42.7.9 with `resultSet.getDate(...)`	0044-03-17	⛔
> '0044-03-15 BC'	driver 42.7.8 as string	`psql`	0044-03-15 BC	✅
> '0044-03-15 BC'	driver 42.7.8 as string	driver 42.7.8 with `resultSet.getString(...)`	0044-03-15 BC	✅
> '0044-03-15 BC'	driver 42.7.8 as string	driver 42.7.8 with `resultSet.getDate(...)`	0044-03-15	±
> '0044-03-15 BC'	driver 42.7.8 as string	driver 42.7.9 with `resultSet.getString(...)`	0044-03-15 BC	✅
> '0044-03-15 BC'	driver 42.7.8 as string	driver 42.7.9 with `resultSet.getDate(...)`	0044-03-17	⚠️
> '0044-03-15 BC'	driver 42.7.9 as string	`psql`	0044-03-15 BC	✅
> '0044-03-15 BC'	driver 42.7.9 as string	driver 42.7.8 with `resultSet.getString(...)`	0044-03-15 BC	✅
> '0044-03-15 BC'	driver 42.7.9 as string	driver 42.7.8 with `resultSet.getDate(...)`	0044-03-15	±
> '0044-03-15 BC'	driver 42.7.9 as string	driver 42.7.9 with `resultSet.getString(...)`	0044-03-15 BC	✅
> '0044-03-15 BC'	driver 42.7.9 as string	driver 42.7.9 with `resultSet.getDate(...)`	0044-03-17	⚠️
> '0044-03-15 BC'	driver 42.7.8 as `sql.Date`¹	`psql`	0044-03-15 BC	✅
> '0044-03-15 BC'	driver 42.7.8 as `sql.Date`¹	driver 42.7.8 with `resultSet.getString(...)`	0044-03-15 BC	✅
> '0044-03-15 BC'	driver 42.7.8 as `sql.Date`¹	driver 42.7.8 with `resultSet.getDate(...)`	0044-03-15	±
> '0044-03-15 BC'	driver 42.7.8 as `sql.Date`¹	driver 42.7.9 with `resultSet.getString(...)`	0044-03-15 BC	✅
> '0044-03-15 BC'	driver 42.7.8 as `sql.Date`¹	driver 42.7.9 with `resultSet.getDate(...)`	0044-03-17	⚠️
> '0044-03-15 BC'	driver 42.7.9 as `sql.Date`¹	`psql`	0044-03-13 BC	⛔
> '0044-03-15 BC'	driver 42.7.9 as `sql.Date`¹	driver 42.7.8 with `resultSet.getString(...)`	0044-03-13 BC	⚠️
> '0044-03-15 BC'	driver 42.7.9 as `sql.Date`¹	driver 42.7.8 with `resultSet.getDate(...)`	0044-03-13	⚠️
> '0044-03-15 BC'	driver 42.7.9 as `sql.Date`¹	driver 42.7.9 with `resultSet.getString(...)`	0044-03-13 BC	⛔
> '0044-03-15 BC'	driver 42.7.9 as `sql.Date`¹	driver 42.7.9 with `resultSet.getDate(...)`	0044-03-15	±
> ¹ done through `java.sql.Date.valueOf(LocalDate.of(-43, 3, 15))`, because `java.sql.Date.valueOf("-0044-03-15")` does not parse (`java.sql.Date` originally did not support BC dates, see it's Javadoc).
> 
> The rows denoted with ± show a known limitiation of `java.sql.Date`. The epoch delivered by the `java.sql.Date#getTime()` is correct for those cases (i.e., different epoch for BC/AD dates that have the same `#toString()` representation).
> 
> The originally reported problem is all the rows denoted with ⚠️ Please also note that 42.7.9 delivers a different epoch for BC dates than 42.7.8.
> 
> The now discovered hard-cut bug are the rows denoted with ⛔.
> 
> This effectively means that if a client has written such dates into a database with 42.7.9, they get incorrect results in both `psql` and via JDBC with previous driver versions. Not only that but querying the same data and accessing the result set either with `getString(...)` or `getDate(...)` will potentially deliver different values.
> 
> Vica versa if a client is writing such values with `psql` they get incorrect results with driver 42.7.9, but got correct results with previous driver versions.
> 
> **THUS AS SUMMARY:** the PostgreSQL JDBC driver as of version 42.7.9 has a _different semantics_ for pre-Gregorian dates than `psql` itself, which is a huge red flag, and I would consider it a bug. If e.g., I have a query
> 
> ```
> select * from testtable where this_is_a_date <= '1000-01-01';
> ```
> 
> I will get different results of the same data, depending on whether I am executing this query with `psql`, with JDBC pre 42.7.9 or with JDBC 42.7.9. It even depends on whether I insert/query the dates with driver 42.7.9 as strings (i.e., `'1000-01-01'`) or as `sql.Date` (i.e., `java.sql.Date.valueOf(LocalDate.of(1000, 1, 1))`) in my `PreparedStatement`s!
> 
> And that is simply put: wrong.

Is there any reason why you are not using `preparedStatement.setObject(int, LocalDate)`?
`java.sql.Date.valueOf(LocalDate)` is unfortunately internally using the deprecated `Date(int, int, int)` constructor. This means the `LocalDate` instance (which is always proleptic Gregorian) is actually interpreted using the Julian calendar if the year is < 1582 (look at `java.util.Date#getCalendarSystem(int)` to see what I mean) and there is no way to control the calendar system in this case.