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: Tue, 10 Feb 2026 15:15:40 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
> [@m-van-tilburg](https://github.com/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
It might be the case that I missed things and not everything is consistent in 42.7.9 as it should be. I can't verify your mentioned timestamps because I don't know the calendar system and time zone used when you created your Date instance. I believe the difference for your Date/Timestamp is caused by how you interpret/parse the String "1000-01-01" to a Date/Timestamp and the other way around. As I tried to demonstrate in the example code in #3837, this is very easy to get wrong with the legacy date/time classes because they are not explicit about the calendar system (also mixing them up by default) and time zone as `java.time` is. Below are three options to create a Date (unique point in time/instant) from the String "1000-01-01" and format it in different ways, which option resembles the way you created your Date?
```
@Test
void dateFromToString() throws ParseException {
// Uncomment the next line if you like to see the influence of the system default time zone on the parsed timestamps
//TimeZone.setDefault(TimeZone.getTimeZone("America/Los_Angeles"));
// Proleptic Gregorian calendar using the system default time zone
final Calendar prolepticGregorianCal = new Calendar.Builder().setCalendarType("iso8601").build();
// Using a hybrid of the Julian and the Gregorian calendar because the default calendar is used!
final SimpleDateFormat sdfJulianGregorianHybrid = new SimpleDateFormat("yyyy-MM-dd");
// Using the proleptic Gregorian calendar
final SimpleDateFormat sdfProlepticGregorian = new SimpleDateFormat("yyyy-MM-dd");
sdfProlepticGregorian.setCalendar(prolepticGregorianCal);
// Since the legacy time zone rules can differ from java.time, ZoneId can't be used. use the legacy raw offset instead
final ZoneOffset calendarZoneOffset = ZoneOffset.ofTotalSeconds(prolepticGregorianCal.getTimeZone().getRawOffset() / 1000);
/* OPTION 1 to parse the date string */
final Date fromSdfJulian = sdfJulianGregorianHybrid.parse("1000-01-01");
// The date is parsed and formatted using the Julian calendar
assertEquals("1000-01-01", sdfJulianGregorianHybrid.format(fromSdfJulian));
// The date is parsed using the Julian calendar and formatted using the Gregorian calendar
assertEquals("1000-01-06", sdfProlepticGregorian.format(fromSdfJulian));
assertEquals("1000-01-06", fromSdfJulian.toInstant().atOffset(calendarZoneOffset).toLocalDate().toString());
/* OPTION 2 to parse the date string */
final Date fromSdfGregorian = sdfProlepticGregorian.parse("1000-01-01");
// The date is parsed using the Gregorian calendar and formatted using the Julian calendar
assertEquals("0999-12-27", sdfJulianGregorianHybrid.format(fromSdfGregorian));
// The date is parsed and formatted using the Gregorian calendar
assertEquals("1000-01-01", sdfProlepticGregorian.format(fromSdfGregorian));
assertEquals("1000-01-01", fromSdfGregorian.toInstant().atOffset(calendarZoneOffset).toLocalDate().toString());
/* OPTION 3 to parse the date string */
final Date fromLocalDateParseGregorian = Date.from(LocalDate.parse("1000-01-01").atStartOfDay().toInstant(calendarZoneOffset));
// The date is parsed using the Gregorian calendar and formatted using the Julian calendar
assertEquals("0999-12-27", sdfJulianGregorianHybrid.format(fromLocalDateParseGregorian));
// The date is parsed and formatted using the Gregorian calendar
assertEquals("1000-01-01", sdfProlepticGregorian.format(fromLocalDateParseGregorian));
assertEquals("1000-01-01", fromLocalDateParseGregorian.toInstant().atOffset(calendarZoneOffset).toLocalDate().toString());
assertEquals(fromSdfGregorian.getTime(), fromLocalDateParseGregorian.getTime());
// Uncomment the next line to see that although the date Strings where the same, the parsed timestamps are NOT
//assertEquals(fromSdfGregorian.getTime(), fromSdfJulian.getTime());
// Date.toString will format it as Julian using the system default time zone
System.out.println(fromLocalDateParseGregorian.toString());
}
```
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