Re: [pgjdbc/pgjdbc] issue #3930: Revert semantic calendar changes introduced with #3837

pgjdbc/pgjdbc GitHub issues and pull requests (mirror)  
help / color / mirror / Atom feed

From: AFulgens (@AFulgens) <[email protected]>
To: pgjdbc/pgjdbc <[email protected]>
Subject: Re: [pgjdbc/pgjdbc] issue #3930: Revert semantic calendar changes introduced with #3837
Date: Fri, 06 Feb 2026 09:24:05 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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

view thread (24+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: github://pgjdbc/pgjdbc
  Cc: [email protected], [email protected]
  Subject: Re: [pgjdbc/pgjdbc] issue #3930: Revert semantic calendar changes introduced with #3837
  In-Reply-To: <<[email protected]>>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox