pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feedFrom: m-van-tilburg (@m-van-tilburg) <[email protected]>
To: pgjdbc/pgjdbc <[email protected]>
Subject: Re: [pgjdbc/pgjdbc] issue #3930: Revert semantic calendar changes introduced with #3837
Date: Mon, 09 Feb 2026 15:29:10 +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:
>
> 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.
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