pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] issue #3924: 0001-01-01 Date not properly saved in database and not properly red from database
9+ messages / 2 participants
[nested] [flat]
* [pgjdbc/pgjdbc] issue #3924: 0001-01-01 Date not properly saved in database and not properly red from database
@ 2026-02-02 10:15 "damixd1 (@damixd1)" <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: damixd1 (@damixd1) @ 2026-02-02 10:15 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
**Describe the issue**
`ResultSet.getDate` or `ResultSet.getTimestamp` returns not correct date when reading value `0001-01-01` (`DATE` column type) from database, is it red like `0000-01-03`. When you want to save `0001-01-01` value in database (`DATE` column type) is is saved as `0001-12-30 BC` (Before Christus). Might have been caused by https://github.com/pgjdbc/pgjdbc/pull/3887
**Driver Version?**
42.7.9
**Java Version?**
21
**OS Version?**
Windows 11 / Linux
**PostgreSQL Version?**
14.17
**To Reproduce**
Steps to reproduce the behaviour:
Put the value `0001-01-01` in a `DATE` column
Execute a query that selects the `DATE` column
Use `ResultSet.getTimestamp()` or `ResultSet.getDate()` to get the value
**Expected behaviour**
Correct historical date is returned or saved.
**Reproduction source code**
```
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
import java.sql.Timestamp;
import java.time.LocalDate;
import java.sql.Date;
public class TestDate {
public static void main(String []args) throws Exception {
String url = "jdbc:postgresql://localhost:5432/test";
Properties props = new Properties();
props.setProperty("user", "test");
props.setProperty("password", "test");
try ( Connection conn = DriverManager.getConnection(url, props) ){
try ( Statement statement = conn.createStatement() ) {
try (ResultSet rs = statement.executeQuery( "SELECT DATE_COLUMN FROM EXAMPLE_TABLE WHERE ID = 1") ){ //Retrives value 0001-01-01 already persisted in database
if (rs.next())
System.out.println( "Get Date: " + rs.getDate(1)); //Returns 0001-01-03
System.out.println( "Get Timestamp: " + rs.getTimestamp(1)); //Returns 0001-01-03 00:00:00
System.out.println( "Get LocalDate: " + rs.getObject(1, LocalDate.class)); //Returns 0001-01-01
}
}
}
}
}
```
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3924: 0001-01-01 Date not properly saved in database and not properly red from database
@ 2026-02-02 11:09 ` "davecramer (@davecramer)" <[email protected]>
7 siblings, 0 replies; 9+ messages in thread
From: davecramer (@davecramer) @ 2026-02-02 11:09 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Thanks for the report. I can confirm that I can reproduce the issue
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3924: 0001-01-01 Date not properly saved in database and not properly red from database
@ 2026-02-02 13:32 ` "davecramer (@davecramer)" <[email protected]>
7 siblings, 0 replies; 9+ messages in thread
From: davecramer (@davecramer) @ 2026-02-02 13:32 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
There's certainly some funkiness now. Question for you: Do you expect `0001-01-01` to be AD or BC
I created a table with both.
```
-62135596800 | 0001-01-01
-62167219200 | 0001-01-01 BC
```
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3924: 0001-01-01 Date not properly saved in database and not properly red from database
@ 2026-02-02 13:40 ` "damixd1 (@damixd1)" <[email protected]>
7 siblings, 0 replies; 9+ messages in thread
From: damixd1 (@damixd1) @ 2026-02-02 13:40 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Hello, I expect 0001-01-01 to be Anno Domini.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3924: 0001-01-01 Date not properly saved in database and not properly red from database
@ 2026-02-02 14:31 ` "davecramer (@davecramer)" <[email protected]>
7 siblings, 0 replies; 9+ messages in thread
From: davecramer (@davecramer) @ 2026-02-02 14:31 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Using
```
try (Connection conn = DriverManager.getConnection(url, props)) {
try (PreparedStatement ps = conn.prepareStatement("insert into example_table(d) values (?)")){
LocalDate localDate = LocalDate.of(0, 1, 1);
java.sql.Date sqlDate = java.sql.Date.valueOf(localDate);
ps.setDate(1, sqlDate);
ps.execute();
localDate = LocalDate.of (1,1,1);
sqlDate = java.sql.Date.valueOf(localDate);
ps.setDate(1, sqlDate);
ps.execute();
}
}
try ( Connection conn = DriverManager.getConnection(url, props) ){
try ( Statement statement = conn.createStatement() ) {
try (ResultSet rs = statement.executeQuery( "SELECT d FROM EXAMPLE_TABLE") ){ //Retrives value 0001-01-01 already persisted in database
while (rs.next()) {
d = rs.getDate(1);
ts = rs.getTimestamp(1);
d1 = rs.getObject(1, LocalDate.class);
//-62135751600000
System.out.println( "Get Date: " + d); //Returns 0001-01-03
System.out.println( "Get Timestamp: " + ts); //Returns 0001-01-03 00:00:00
System.out.println( "Get LocalDate: " + d1); //Returns 0001-01-01
}
}
}
}
}
```
So with 42.7.8 given a table:
```
table example_table;
id | d
----+---------------
16 | 0001-01-01 BC
17 | 0001-01-01
```
the code produces
```
Get Date: 0001-01-01
Get Timestamp: 0001-01-01 00:00:00.0
Get LocalDate: 0000-01-01
Get Date: 0001-01-01
Get Timestamp: 0001-01-01 00:00:00.0
Get LocalDate: 0001-01-01
```
which is clearly wrong as they are 2 different dates.
With 42.7.9 we get
```
table example_table;
id | d
----+---------------
18 | 0002-12-30 BC
19 | 0001-12-30 BC
```
```
Get Date: 0001-01-01
Get Timestamp: 0001-01-01 00:00:00.0
Get LocalDate: -0001-12-30
Get Date: 0001-01-01
Get Timestamp: 0001-01-01 00:00:00.0
Get LocalDate: 0000-12-30
```
Which is also clearly wrong.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3924: 0001-01-01 Date not properly saved in database and not properly red from database
@ 2026-02-02 16:59 ` "davecramer (@davecramer)" <[email protected]>
7 siblings, 0 replies; 9+ messages in thread
From: davecramer (@davecramer) @ 2026-02-02 16:59 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
I spent a fair amount of time on this and while the code used to work. The only reliable way to get this to work is to use
setObject(1,LocalDate) and getObject(1,LocalDate)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3924: 0001-01-01 Date not properly saved in database and not properly red from database
@ 2026-02-03 12:07 ` "davecramer (@davecramer)" <[email protected]>
7 siblings, 0 replies; 9+ messages in thread
From: davecramer (@davecramer) @ 2026-02-03 12:07 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
I don't believe this is possible to fix, at least not using getDate. get/set Object with LocalDate.class works
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3924: 0001-01-01 Date not properly saved in database and not properly red from database
@ 2026-02-12 13:36 ` "damixd1 (@damixd1)" <[email protected]>
7 siblings, 0 replies; 9+ messages in thread
From: damixd1 (@damixd1) @ 2026-02-12 13:36 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@davecramer I do not think this is a good idea to close this issue, while problem still exits and was caused by https://github.com/pgjdbc/pgjdbc/pull/3887
"get/set Object with LocalDate.class works" - that would require changes in all libraries that use those methods, for example Hibernate. I do not think this is a way to go.
In my opinion, https://github.com/pgjdbc/pgjdbc/pull/3887 should be reverted at least, and then problem fixed comprehensively later.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3924: 0001-01-01 Date not properly saved in database and not properly red from database
@ 2026-02-12 13:42 ` "davecramer (@davecramer)" <[email protected]>
7 siblings, 0 replies; 9+ messages in thread
From: davecramer (@davecramer) @ 2026-02-12 13:42 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
#3887 has been reverted and a new release has been created.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2026-02-12 13:42 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-02 10:15 [pgjdbc/pgjdbc] issue #3924: 0001-01-01 Date not properly saved in database and not properly red from database "damixd1 (@damixd1)" <[email protected]>
2026-02-02 11:09 ` "davecramer (@davecramer)" <[email protected]>
2026-02-02 13:32 ` "davecramer (@davecramer)" <[email protected]>
2026-02-02 13:40 ` "damixd1 (@damixd1)" <[email protected]>
2026-02-02 14:31 ` "davecramer (@davecramer)" <[email protected]>
2026-02-02 16:59 ` "davecramer (@davecramer)" <[email protected]>
2026-02-03 12:07 ` "davecramer (@davecramer)" <[email protected]>
2026-02-12 13:36 ` "damixd1 (@damixd1)" <[email protected]>
2026-02-12 13:42 ` "davecramer (@davecramer)" <[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