pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] issue #3837: ResultSet.getTimestamp() returns incorrect point in time for historical dates
13+ messages / 3 participants
[nested] [flat]
* [pgjdbc/pgjdbc] issue #3837: ResultSet.getTimestamp() returns incorrect point in time for historical dates
@ 2025-10-02 11:50 "m-van-tilburg (@m-van-tilburg)" <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: m-van-tilburg (@m-van-tilburg) @ 2025-10-02 11:50 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
**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()));
}
}
}
}
```
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3837: ResultSet.getTimestamp() returns incorrect point in time for historical dates
@ 2025-10-02 12:48 "davecramer (@davecramer)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: davecramer (@davecramer) @ 2025-10-02 12:48 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Interesting problem. How would you propose we solve how to figure out what calendar to use?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3837: ResultSet.getTimestamp() returns incorrect point in time for historical dates
@ 2025-10-02 14:12 "m-van-tilburg (@m-van-tilburg)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: m-van-tilburg (@m-van-tilburg) @ 2025-10-02 14:12 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> 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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3837: ResultSet.getTimestamp() returns incorrect point in time for historical dates
@ 2025-10-02 14:18 "davecramer (@davecramer)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: davecramer (@davecramer) @ 2025-10-02 14:18 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
and your suggestion is ?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3837: ResultSet.getTimestamp() returns incorrect point in time for historical dates
@ 2025-10-02 14:32 "m-van-tilburg (@m-van-tilburg)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: m-van-tilburg (@m-van-tilburg) @ 2025-10-02 14:32 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> 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());`
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3837: ResultSet.getTimestamp() returns incorrect point in time for historical dates
@ 2025-10-13 09:15 "ShenFeng312 (@ShenFeng312)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: ShenFeng312 (@ShenFeng312) @ 2025-10-13 09:15 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3837: ResultSet.getTimestamp() returns incorrect point in time for historical dates
@ 2025-10-13 11:45 "m-van-tilburg (@m-van-tilburg)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: m-van-tilburg (@m-van-tilburg) @ 2025-10-13 11:45 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> 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`.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3837: ResultSet.getTimestamp() returns incorrect point in time for historical dates
@ 2025-11-21 09:26 "m-van-tilburg (@m-van-tilburg)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: m-van-tilburg (@m-van-tilburg) @ 2025-11-21 09:26 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Is there anyway I can help with this issue?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3837: ResultSet.getTimestamp() returns incorrect point in time for historical dates
@ 2025-11-21 09:28 "davecramer (@davecramer)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: davecramer (@davecramer) @ 2025-11-21 09:28 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> 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?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3837: ResultSet.getTimestamp() returns incorrect point in time for historical dates
@ 2025-11-21 09:58 "m-van-tilburg (@m-van-tilburg)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: m-van-tilburg (@m-van-tilburg) @ 2025-11-21 09:58 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
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).
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3837: ResultSet.getTimestamp() returns incorrect point in time for historical dates
@ 2025-11-21 10:13 "davecramer (@davecramer)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: davecramer (@davecramer) @ 2025-11-21 10:13 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
can you propose a fix and a pull request ?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3837: ResultSet.getTimestamp() returns incorrect point in time for historical dates
@ 2025-11-21 10:34 "m-van-tilburg (@m-van-tilburg)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: m-van-tilburg (@m-van-tilburg) @ 2025-11-21 10:34 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> can you propose a fix and a pull request ?
Yes, I will start working on a fix and related unit tests soon.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3837: ResultSet.getTimestamp() returns incorrect point in time for historical dates
@ 2025-12-04 15:06 "m-van-tilburg (@m-van-tilburg)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: m-van-tilburg (@m-van-tilburg) @ 2025-12-04 15:06 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@davecramer I linked a pull request that should fix this issue, please let me know if you have any questions or remarks
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2025-12-04 15:06 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-02 11:50 [pgjdbc/pgjdbc] issue #3837: ResultSet.getTimestamp() returns incorrect point in time for historical dates "m-van-tilburg (@m-van-tilburg)" <[email protected]>
2025-10-02 12:48 ` "davecramer (@davecramer)" <[email protected]>
2025-10-02 14:12 ` "m-van-tilburg (@m-van-tilburg)" <[email protected]>
2025-10-02 14:18 ` "davecramer (@davecramer)" <[email protected]>
2025-10-02 14:32 ` "m-van-tilburg (@m-van-tilburg)" <[email protected]>
2025-10-13 09:15 ` "ShenFeng312 (@ShenFeng312)" <[email protected]>
2025-10-13 11:45 ` "m-van-tilburg (@m-van-tilburg)" <[email protected]>
2025-11-21 09:26 ` "m-van-tilburg (@m-van-tilburg)" <[email protected]>
2025-11-21 09:28 ` "davecramer (@davecramer)" <[email protected]>
2025-11-21 09:58 ` "m-van-tilburg (@m-van-tilburg)" <[email protected]>
2025-11-21 10:13 ` "davecramer (@davecramer)" <[email protected]>
2025-11-21 10:34 ` "m-van-tilburg (@m-van-tilburg)" <[email protected]>
2025-12-04 15:06 ` "m-van-tilburg (@m-van-tilburg)" <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox