Subject: [pgjdbc/pgjdbc] issue #3837: ResultSet.getTimestamp() returns incorrect point in time for historical dates

From: "m-van-tilburg (@m-van-tilburg)" <[email-masked]>
Date: Thu, 02 Oct 2025 11:50:56 +0000

**Describe the issue**
`ResultSet.getTimestamp()` returns incorrect point in time for historical dates.

**Driver Version?** 
42.7.7.

**Java Version?**
21.0.2.

**OS Version?**
Windows 10 Pro.

**PostgreSQL Version?**
17.4

**To Reproduce**
Steps to reproduce the behaviour:

1. Put the value '1000-01-01 00:00:00' in a TIMESTAMP column
2. Execute a query that selects the TIMESTAMP column
3. Use `ResultSet.getTimestamp()` to get the value as a `Timestamp` object

**Expected behaviour**
The expected behaviour is that a `Timestamp` is returned that represents the correct point in time.
But currently a `Timestamp` is returned that appears to be incorrectly created using the Julian calendar instead of the (proleptic) Gregorian calendar.

**Reproduction source code**

```
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.sql.Types;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.util.Calendar;
import java.util.GregorianCalendar;

import static org.junit.jupiter.api.Assertions.assertEquals;

class PgJdbcTest {

  private static final LocalDateTime LDT_LOCAL_1000_AD = LocalDateTime.of(1000, 1, 1, 0, 0, 0, 0);
  private static final ZonedDateTime ZDT_LOCAL_1000_AD = ZonedDateTime.of(LDT_LOCAL_1000_AD, ZoneId.systemDefault());
  private static final Timestamp TIMESTAMP_LOCAL_1000_AD = Timestamp.from(ZDT_LOCAL_1000_AD.toInstant());
  // GregorianCalendar.from(ZonedDateTime) will create a pure (proleptic) Gregorian calendar
  private static final Calendar CALENDAR_LOCAL_1000_AD = GregorianCalendar.from(ZDT_LOCAL_1000_AD);

  @Test
  void timestampColumn() throws Exception {
    try (Connection dbConnection = DriverManager.getConnection("jdbc:postgresql://localhost:18094/cds", "bq_pg_cds", "bq_pg_cds_01##")) {
      dbConnection.createStatement().execute("DELETE FROM public.timestamp_test");

      PreparedStatement preparedStatement = dbConnection.prepareStatement("INSERT INTO public.timestamp_test (my_id, my_timestamp) VALUES (?, ?)");
      preparedStatement.setLong(1, 1);
//      preparedStatement.setTimestamp(2, TIMESTAMP_LOCAL_1000_AD, CALENDAR_LOCAL_1000_AD);
      preparedStatement.setObject(2, LDT_LOCAL_1000_AD, Types.TIMESTAMP);
      preparedStatement.execute();

      ResultSet resultSet = dbConnection.createStatement().executeQuery("SELECT my_id, my_timestamp FROM public.timestamp_test");
      while (resultSet.next()) {
        LocalDateTime resultLocalDateTime = resultSet.getObject("my_timestamp", LocalDateTime.class);
        assertEquals(LDT_LOCAL_1000_AD, resultLocalDateTime);

        Timestamp resultTimestamp = resultSet.getTimestamp("my_timestamp", CALENDAR_LOCAL_1000_AD);
//        assertEquals(TIMESTAMP_LOCAL_1000_AD.toInstant(), resultTimestamp.toInstant());
        /*
          Tested this with time zone 'Europe/Amsterdam' resulting in:
            Expected : 1000-01-01T00:00+00:17:30[Europe/Amsterdam]
            Actual   : 1000-01-05T23:17:30+00:17:30[Europe/Amsterdam]
          The timestamp is correct in the database but when returned using resultSet.getTimestamp():
            1. The day is off because the Julian instead of the (proleptic) Gregorian calendar was used
            2. The time is off because the time zone rule handling of java.util.TimeZone is different from java.time
         */
        assertEquals(ZDT_LOCAL_1000_AD, resultTimestamp.toInstant().atZone(ZoneId.systemDefault()));
      }
    }
  }

}
```

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 02 Oct 2025 12:48:51 +0000

Interesting problem. How would you propose we solve how to figure out what calendar to use?

---

From: "m-van-tilburg (@m-van-tilburg)" <[email-masked]>
Date: Thu, 02 Oct 2025 14:12:14 +0000

> Interesting problem. How would you propose we solve how to figure out what calendar to use?

According to https://www.postgresql.org/docs/17/datetime-units-history.html all dates are treated as (proleptic) Gregorian. The JDBC API also suggests that provided `Calendar` objects are used to only get time zone information from. Code like `private final Calendar calendarWithUserTz = new GregorianCalendar();` in `org.postgresql.jdbc.TimestampUtils` appears to have been written in the assumption that this creates a pure (proleptic) Gregorian calendar. However, using `new GregorianCalendar()` actually creates a calendar that is a hybrid of the Julian and the Gregorian calendar using the default change date of October 15, 1582 (Gregorian). Read `java.util.GregorianCalendar#setGregorianChange(java.util.Date)` for more information.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 02 Oct 2025 14:18:34 +0000

and your suggestion is ?

---

From: "m-van-tilburg (@m-van-tilburg)" <[email-masked]>
Date: Thu, 02 Oct 2025 14:32:08 +0000

> and your suggestion is ?

To use a pure (proleptic) Gregorian calendar while doing date/time conversions that involve `java.util.Calendar`, one of the ways to create one is using `new Calendar.Builder().setCalendarType("iso8601").build());`

---

From: "ShenFeng312 (@ShenFeng312)" <[email-masked]>
Date: Mon, 13 Oct 2025 09:15:12 +0000

his issue seems unrelated to JDBC. I observed that `TIMESTAMP_LOCAL_1000_AD` is already incorrect. It also seems to be caused by JDK behavior.

---

From: "m-van-tilburg (@m-van-tilburg)" <[email-masked]>
Date: Mon, 13 Oct 2025 11:45:56 +0000

> his issue seems unrelated to JDBC. I observed that `TIMESTAMP_LOCAL_1000_AD` is already incorrect. It also seems to be caused by JDK behavior.

You are correct that the issue is not in the JDBC API itself, the created point in time in the test code is correct however. You can verify this by inputting the value of `TIMESTAMP_LOCAL_1000_AD.getTime()` into a website like https://www.epochconverter.com/.

I suspect you came to your conclusion because you saw the the result of `Timestamp.toString()` in your debugger, which will interpret this (historic) point in time using the Julian calendar. `java.util.Date` and `java.sql.Timestamp` store points in time just like `java.time.Instant`, the important part is how these points in time are parsed/formatted from/to actual calendar dates. The following additional code using the legacy `java.util.Calendar` class demonstrates this:

```
  @Test
  void legacyCalendarExamples() {
    // NOTE: it may appear this creates a (proleptic) Gregorian calendar but for historic dates it is Julian!
    GregorianCalendar calGregorianJulianHybrid = new GregorianCalendar();
    calGregorianJulianHybrid.setTime(TIMESTAMP_LOCAL_1000_AD);
    System.out.println("Julian calendar date and time: " + formatCalendar(calGregorianJulianHybrid));

    GregorianCalendar calProlepticGregorian = new GregorianCalendar();
    // This will make the calendar pure (proleptic) Gregorian
    calProlepticGregorian.setGregorianChange(new Date(Long.MIN_VALUE));
    calProlepticGregorian.setTime(TIMESTAMP_LOCAL_1000_AD);
    System.out.println("(proleptic) Gregorian calendar date and time: " + formatCalendar(calProlepticGregorian));
  }

  private String formatCalendar(Calendar cal) {
    // NOTE: Calendar.MONTH is zero based
    return cal.get(Calendar.YEAR) + "-" + (cal.get(Calendar.MONTH) + 1) + "-" + cal.get(Calendar.DAY_OF_MONTH) + "T" +
      cal.get(Calendar.HOUR_OF_DAY) + ":" + cal.get(Calendar.MINUTE) + ":" + cal.get(Calendar.SECOND);
  }
```

On my system with time zone set to "Europe/Amsterdam" this produces the following result:

Julian calendar date and time: 999-12-27T0:42:30
(proleptic) Gregorian calendar date and time: 1000-1-1T0:42:30

Note that although `java.util.GregorianCalendar` by default uses the system default time zone the results are not always identical to when `java.time.ZonedDateTime` with the system default time zone is used. The reason for this is that the time zone rule handling for historical dates can be different between `java.util.TimeZone` and `java.time`.

---

From: "m-van-tilburg (@m-van-tilburg)" <[email-masked]>
Date: Fri, 21 Nov 2025 09:26:37 +0000

Is there anyway I can help with this issue?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 21 Nov 2025 09:28:31 +0000

> Is there anyway I can help with this issue?

Well your previous reply confused me. Is the current code correct, or do we need to fix something?

---

From: "m-van-tilburg (@m-van-tilburg)" <[email-masked]>
Date: Fri, 21 Nov 2025 09:58:05 +0000

The current `pgjdbc` code is incorrect because instances of `java.util.Calendar` are created (for example with `Calendar.getInstance()` or `new GregorianCalendar()`) without making the calendar pure (proleptic) Gregorian using `java.util.GregorianCalendar#setGregorianChange(java.util.Date)`. The currently created calendar instances are a hybrid of the Julian and Gregorian calendar which is usually not appropriate in a JDBC driver, they should be pure (proleptic) Gregorian instead (to match what is described in https://www.postgresql.org/docs/17/datetime-units-history.html).

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 21 Nov 2025 10:13:40 +0000

can you propose a fix and a pull request ?

---

From: "m-van-tilburg (@m-van-tilburg)" <[email-masked]>
Date: Fri, 21 Nov 2025 10:34:04 +0000

> can you propose a fix and a pull request ?

Yes, I will start working on a fix and related unit tests soon.

---

From: "m-van-tilburg (@m-van-tilburg)" <[email-masked]>
Date: Thu, 04 Dec 2025 15:06:58 +0000

@davecramer I linked a pull request that should fix this issue, please let me know if you have any questions or remarks