Message-ID: <gh-pgjdbc-pgjdbc-3930-c3859040924@github.com>
From: "AFulgens (@AFulgens)" <noreply+AFulgens@github.com>
To: "pgjdbc/pgjdbc" <noreply+pgjdbc-pgjdbc@github.com>
Date: Fri, 06 Feb 2026 09:24:05 +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: AFulgens
X-GitHub-Comment-Id: 3859040924
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-3859040924
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:

<details>
<summary>Detailed test results</summary>

| 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).

</details>

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.