Message-ID: From: "AFulgens (@AFulgens)" To: "pgjdbc/pgjdbc" Date: Tue, 10 Feb 2026 12:36:22 +0000 Subject: Re: [pgjdbc/pgjdbc] issue #3930: Revert semantic calendar changes introduced with #3837 In-Reply-To: References: List-Id: X-GitHub-Author-Login: AFulgens X-GitHub-Comment-Id: 3877333948 X-GitHub-Comment-Type: issue_comment X-GitHub-Edited-At: 2026-02-10T13:22:07Z X-GitHub-Issue: 3930 X-GitHub-Repo: pgjdbc/pgjdbc X-GitHub-Type: comment X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/3930#issuecomment-3877333948 Content-Type: text/plain; charset=utf-8 @m-van-tilburg I am answering your comments in a summarized manner instead of point-by-point, I hope that's OK with you :) > I hope my well intended contribution to "pgjdbc" did not cause too much problems for your historic dates, I'm sorry if it did. I totally see your point and I see that it was well intended. I see why the semantic inconsistency is "painful" for Julian vs. Gregorian dates when working with the epochs. However, it breaks already existing systems with such data. An even bigger problem is that it introduces an inconsistency on the API-level and incompatibility with `psql`, which is the _de facto_ standard of how stored data shall be interpreted. > Is there any reason why you are not using preparedStatement.setObject(int, LocalDate)? I am working at a company where we use a lot of microservices. I cannot prescribe for each of my developers how do they use JDBC. Some projects are working with pure SQL and `PreparedStatements`, however they see fit, they can access the data. Other projects are working with jOOQ, Hibernate, or Spring. My premise is that I want data to be consistent, regardless how it is inserted and queried. > I am not sure if you are aware but support for java.time classes (using preparedStatement.setObject() and resultSet.getObject()) was added in JDBC 4.2 (Java 8) Support, yes, but it's not baked in on the lowest levels, that's what I mean with my tongue-in-cheek comment. > As a side note, I tested the legacy date/time classes behavior with three JDBC drivers, "pgjdbc", "mssql-jdbc" and "ojdbc" (Oracle). As detailed in this issue, pgjdbc with version 42.7.9 is inconsistent. Based on your comment now I have tested with mssql-jdbc (13.3.1.jre11-preview against SQL Server 2025, i.e., 17.00.4006) and it is currently consistent across all possible access methods apart from `OffsetDateTime`. Inserting ODT with `ZoneOffset.UTC` results in '0999-12-27 00:00:00.0000000'. Funnily `getObject(OffsetDateTime)` throws `Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from datetime2 to DATETIMEOFFSET is unsupported.`, so the support seems to be incomplete in the driver. I have also tested with oracle-jdbc (ojdbc17:23.26.1.0.0 against AI Database 26ai Free, i.e., 23.26.1.0.0 with nls_calendar=gregorian) and it is currently consistent across all possible access methods apart from `OffsetDateTime`. Querying ODT results in `Exception in thread "main" java.sql.SQLException: ORA-18716: {0} not in any time zone.DATE`. Inserting works and is stored as expected. > LocalDateTime / OffsetDateTime DATE columns cannot be converted into LDT/ODT, you get an exception: ``` Exception in thread "main" org.postgresql.util.PSQLException: Cannot convert the column of type DATE to requested type java.time.[Local|Offset]DateTime. ``` Nevertheless I tested now with a DATE and with a TIMESTAMP column. I won't provide all the combinations but here is the gist of it: Everything is consistent with driver version 42.7.8, i.e., * I insert '1000-01-01' with `psql`, via `setDate(java.sql.Date)`, via `setObject(LocalDate)`, or directly as string * and I get back the same date, regardless whether I query it in `psql`, via `getString`, `getDate`, or `getObject(LocalDate)` * I insert '1000-01-01 00:00:00' with `psql`, via `setTimestamp(java.sql.Timestamp)`, via `setObject(LocalDateTime)`, via `setObject(OffsetDateTime)`, or directly as string * and I get back the same time, regardless whether I query it in `psql`, via `getString`, `getDate`, `getTimestamp`, `getObject(LocalDateTime)`, or `getObject(OffsetDateTime)` * there is one discrepancy, namely inserting as `OffsetDateTime` results in `1000-01-01 00:34:08` when using `ZoneOffset.UTC`, but time zones are messy * for reference, returned epoch is -30609795600000. (This is the same epoch as the MSSQL or the Oracle driver returns!) If I query such inserted data with 42.7.9, I get: * inserted with `psql` returns correctly for `getString`, `getObject(LocalDate)`, `getObject(LocalDateTime), OffsetDateTime` as '1000-01-01', but as `getDate|Timestamp` it returns '999-12-27'. The other way, if inserts are done with version 42.7.9: * `psql` and former driver versions return '1000-01-06' but only if the insert has been done via `setDate|Timestamp`, all the others, i.e., as string or `setObject(LD(T)|ODT)` return '1000-01-01' * driver version 42.7.9 returns '1000-01-01' for data inserted via `psql` if queried as string, or LD/LDT/ODT, but '999-12-27' if queried with `getDate|Timestamp`. * driver version 42.7.9 returns '1000-01-06' for data inserted via driver 42.7.9 via `setDate|Timestamp` but queried as string, LD/LDT/ODT; returns '1000-01-01' for the same data if queried with `getDate|Timestamp`. * etc. pp. according to original report
Stub code ```java var metaData = connection.getMetaData(); System.out.println("Driver Name: " + metaData.getDriverName()); System.out.println("Driver Version: " + metaData.getDriverVersion()); System.out.println("Driver Major Version: " + metaData.getDriverMajorVersion()); System.out.println("Driver Minor Version: " + metaData.getDriverMinorVersion()); System.out.println("Database Product: " + metaData.getDatabaseProductName()); System.out.println("Database Version: " + metaData.getDatabaseProductVersion()); System.out.println("JDBC Version: " + metaData.getJDBCMajorVersion() + "." + metaData.getJDBCMinorVersion()); System.out.println("================================"); boolean doInsert = false; if (doInsert) { String insert; insert = "insert into public.testtable(this_is_a_date, this_is_a_time, inserted_via) values (?, ?, ?)"; statement = connection.prepareStatement(insert); statement.setDate(1, java.sql.Date.valueOf("1000-01-01")); statement.setTimestamp(2, java.sql.Timestamp.valueOf("1000-01-01 00:00:00")); statement.setString(3, "set(Date|Timestamp)/java.sql.(Date|Timestamp)"); statement.executeUpdate(); insert = "insert into public.testtable(this_is_a_date, this_is_a_time, inserted_via) values (?, ?, ?)"; statement = connection.prepareStatement(insert); statement.setObject(1, LocalDate.of(1000, 1, 1)); statement.setObject(2, LocalDateTime.of(1000, 1, 1, 0, 0, 0, 0)); statement.setString(3, "setObject/LD(T)"); statement.executeUpdate(); statement = connection.prepareStatement(insert); statement.setObject(1, LocalDate.of(1000, 1, 1)); statement.setObject(2, OffsetDateTime.of(1000, 1, 1, 0, 0, 0, 0, ZoneOffset.UTC)); statement.setString(3, "setObject/ODT"); statement.executeUpdate(); insert = "insert into public.testtable(this_is_a_date, this_is_a_time, inserted_via) values ('1000-01-01', '1000-01-01T00:00:00Z', 'as string')"; statement = connection.prepareStatement(insert); statement.executeUpdate(); } String select = "SELECT * FROM public.testtable"; statement = connection.prepareStatement(select); resultSet = statement.executeQuery(); while (resultSet.next()) { String via = resultSet.getString(4); String str = resultSet.getString(2); java.sql.Date date = resultSet.getDate(2); LocalDate ld = resultSet.getObject(2, LocalDate.class); java.sql.Timestamp ts = resultSet.getTimestamp(3); LocalDateTime ldt = resultSet.getObject(3, LocalDateTime.class); OffsetDateTime odt = resultSet.getObject(3, OffsetDateTime.class); System.out.println("Inserted via: " + via); System.out.println("As String: " + str); System.out.println("As sql.Date: " + date); System.out.println("Epoch within sql.Date: " + date.getTime()); System.out.println("As sql.Timestamp: " + ts); System.out.println("Epoch within sql.Timestamp: " + ts.getTime()); System.out.println("As LD:" + ld); System.out.println("As LDT:" + ldt); System.out.println("As ODT:" + odt); } ```