Message-ID: <gh-pgjdbc-pgjdbc-3930-c3872256038@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:02:02 +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: 3872256038
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-3872256038
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.

I noticed that `resultSet.getObject("my_timestamp", LocalDateTime.class)` and `resultSet.getObject("my_timestamp", OffsetDateTime.class)` are not in your test results, how do they compare to the other methods? Also I wonder how you obtained the string value for `resultSet.getDate(...)`, are you aware that `java.util.Date.toString()` formats its timestamp value using the Julian calendar for dates before the default cutover date?

I can understand that you consider using the proleptic Gregorian calendar (like the SQL standard and `java.time` describe) for the legacy Java date/time classes a breaking change, because the "pgjdbc" driver (accidentally) did not do this from the start. I hope my well intended contribution to "pgjdbc" did not cause too much problems for your historic dates, I'm sorry if it did.