pgjdbc/pgjdbc GitHub issues and pull requests (mirror)  

help / color / mirror / Atom feed

From: m-van-tilburg (@m-van-tilburg) <[email protected]>
To: pgjdbc/pgjdbc <[email protected]>
Subject: [pgjdbc/pgjdbc] issue #3837: ResultSet.getTimestamp() returns incorrect point in time for historical dates
Date: Thu, 02 Oct 2025 11:50:56 +0000
Message-ID: <[email protected]> (raw)

**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()));
      }
    }
  }

}
```

view thread (13+ 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 #3837: ResultSet.getTimestamp() returns incorrect point in time for historical dates
  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