Message-ID: From: "m-van-tilburg (@m-van-tilburg)" To: "pgjdbc/pgjdbc" Date: Tue, 10 Feb 2026 17:30:50 +0000 Subject: Re: [pgjdbc/pgjdbc] issue #3930: Revert semantic calendar changes introduced with #3837 In-Reply-To: References: List-Id: X-GitHub-Author-Login: m-van-tilburg X-GitHub-Comment-Id: 3879650049 X-GitHub-Comment-Type: issue_comment X-GitHub-Edited-At: 2026-02-10T18:40:36Z X-GitHub-Issue: 3930 X-GitHub-Repo: pgjdbc/pgjdbc X-GitHub-Type: comment X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/3930#issuecomment-3879650049 Content-Type: text/plain; charset=utf-8 > I provided the code I am using. > > I do not convert any string into a date when executing the query, I am effectively doing > > ``` > resultSet.getDate(...).toString() > resultSet.getString(...) > ``` > > And these must not deliver different results, IMO. Cf. [PoLA](https://en.wikipedia.org/wiki/Principle_of_least_astonishment) > > Can we at least agree that if I execute the following with `psql`: > > ``` > insert into public.testtable(this_is_a_date) values ('1000-01-01'); > ``` > > Then I can expect to get "1000-01-01" regardless of how am I querying the data? (As is the case for the current versions of the MS-SQL, Oracle drivers and for pgjdbc up till 42.7.8) > > My argument is that that `java.sql.Date` and `java.sql.Timestamp` have a semantic issue with pre-Gregorian dates is irrelevant at this point and should not be corrected in the **driver code**. If clients have a problem, they have to correct it after querying it. Otherwise any legacy Java code relying on the current behaviour become effectively broken because of this change. Thus, it can be done, but it **must** be in a major release of the driver, if done at all. Now I understand where the difference is coming from. PostgreSQL uses the proleptic Gregorian calendar (see https://www.postgresql.org/docs/17/datetime-units-history.html) and you format your Date instance using the Julian calendar. - resultSet.getString(...) should return what is actually inside the database, thus always formatted as proleptic Gregorian. - resultSet.getDate(...) should return a point in time/timestamp that matches with the timestamp that was inserted. - resultSet.getDate(...).toString() returns what Sun chose 30 years ago in their unfortunate implementation of `java.util.Date` (which is actually a timestamp), thus formatted as Julian for old dates like "1000-01-01". They tried fixing this later by adding `java.util.GregorianCalendar` which allows for more control, but unfortunately also by default is a hybrid of the Julian and Gregorian calendar. Creating Date instances this way thus also might result in timestamps interpreted using the Julian calendar. - java.sql.Date.valueOf(LocalDate) (which you also used in your examples) was added in Java 8 and may appear to be using proleptic Gregorian to create the internal timestamp value. Unfortunately it uses internal methods that will result in all dates before 1582 to be interpreted using the Julian calendar. What is particularly odd is that the usual month (10) and day (15) do not seem to be considered. Our software uses date calculations and this mixup of calendars surely returned unexpected results when one date was before 1582-10-15 and the other was after it. We fixed it by using `java.time` practically everywhere so the calendar system and time zone rules are explicit and consistent. `java.sql.Date` and `java.sql.Timestamp` don't have semantic issues with pre-Gregorian dates, they only store unique points in time (timestamps) just like `java.time.Instant`. It all depends on how you create your timestamps and how you format those timestamps back into local date strings, in this case avoiding Date.toString(). If like you said, everyone in your company is using different methods to parse/interpret, you are bound to get different timestamps. Depending on the calendar system and time zone you use when formatting those timestamps in a particular piece of code, the Strings might look the same, tricking you in believing the timestamps must be the same too while they are not. I agree the change should be in a major release because what you get back from the server is interpreted different from previous versions of the driver. For microsoft/mssql-jdbc#2789 the same consideration might apply. In 42.7.8 you will get different timestamps depending on how you use the JDBC API. The `java.time` methods will use proleptic Gregorian (like the server). The legacy date/time methods will use a hybrid of Julian and Gregorian because an internal `java.util.Calendar` instance is used that has the default cutover point of 1582-10-15. The fix in 42.7.9 is essentially that the internal `java.util.Calendar` instances are always proleptic Gregorian. This means all clients still using legacy Java date/time classes should interpret/parse/format their timestamps using a proleptic Gregorian `java.util.Calendar` instance, or use `java.time` that is also proleptic Gregorian (different calendars are supported when more advanced Chronology classes are used). I also used `psql` to insert and select, the results are correct, identical with what I got with the following code using 42.7.9 ``` @Test void dateColumn() throws Exception { try (Connection dbConnection = DriverManager.getConnection("jdbc:postgresql://YOUR_DB", "USER", "PASSWORD")) { dbConnection.createStatement().execute("DELETE FROM public.date_test"); dbConnection.createStatement().execute("INSERT INTO public.date_test (my_id, my_date) VALUES (1, '1000-01-01')"); ResultSet resultSet = dbConnection.createStatement().executeQuery("SELECT my_id, my_date FROM public.date_test"); while (resultSet.next()) { assertEquals(LocalDate.of(1000, 1, 1), resultSet.getObject("my_date", LocalDate.class)); assertEquals("1000-01-01", resultSet.getString("my_date")); } } } ```