pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] issue #3943: OffsetDateTime with TIMESTAMP WITH TIME ZONE column always return UTC as ZoneOffset.
2+ messages / 2 participants
[nested] [flat]
* [pgjdbc/pgjdbc] issue #3943: OffsetDateTime with TIMESTAMP WITH TIME ZONE column always return UTC as ZoneOffset.
@ 2026-02-23 06:04 "hgschmie (@hgschmie)" <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: hgschmie (@hgschmie) @ 2026-02-23 06:04 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Please read https://stackoverflow.com/help/minimal-reproducible-example
**Describe the issue**
Storing a OffsetDateTime using setObject() in the postgres driver results in having the right time and offset in the database.
Reading it back from the database works fine, but then the pgjdbc driver simply converts it to UTC. (in this line: https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/jdbc/PgResultSet.ja...)
**Driver Version?**
42.7.9 & 42.7.10
**Java Version?**
openjdk version "25.0.2" 2026-01-20 LTS
OpenJDK Runtime Environment Temurin-25.0.2+10 (build 25.0.2+10-LTS)
OpenJDK 64-Bit Server VM Temurin-25.0.2+10 (build 25.0.2+10-LTS, mixed mode, sharing)
**OS Version?**
OS name: "mac os x", version: "15.7.4", arch: "aarch64", family: "mac"
**PostgreSQL Version?**
17.8, 18.2
**To Reproduce**
Steps to reproduce the behaviour:
Run the program below. Look at input and output.
**Expected behaviour**
I am inserting an OffsetDateTime with a given timezone into `TIMESTAMP WITH TIME ZONE` column.
When setting the session time zone of the database to a time zone and then retrieving a value from a `TIMESTAMP WITH TIME ZONE` column as an OffsetDateTime with getObject() from the ResultSet, I expect the OffsetDateTime to have the time zone of the session that I have set.
Instead, the resulting OffsetTimeStamp uses UTC timezone. The behavior is hardcoded in the pgjdbc driver.
Ironically, when using a `TIME WITH TIME ZONE` column, it works because it goes in the branch on line 692 (https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/jdbc/PgResultSet.ja...) which returns the offsetdatetime.
**Logs**
If possible PostgreSQL logs surrounding the occurrence of the issue
Additionally logs from the driver can be obtained adding
Using the following template code make sure the bug can be replicated in the driver alone.
```
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.time.OffsetDateTime;
import java.time.ZoneId;
import java.util.Properties;
class TestIssue {
public static void main(String []args) throws Exception {
System.setProperty("user.timezone", "Asia/Colombo");
var now = OffsetDateTime.now(ZoneId.systemDefault());
System.out.println("Current time in system default timezone: " + now);
String url = "jdbc:postgresql://localhost:5432/test";
Properties props = new Properties();
props.setProperty("user", "root");
props.setProperty("password", "");
try ( Connection conn = DriverManager.getConnection(url, props) ){
try ( Statement statement = conn.createStatement() ) {
statement.execute("DROP TABLE IF EXISTS foo");
statement.execute("CREATE TABLE foo (t TIMESTAMP WITH TIME ZONE)");
statement.execute("SET TIMEZONE TO 'Asia/Colombo'");
var p = conn.prepareStatement("insert into foo values (?)");
p.setObject(1, now);
p.execute();
try (ResultSet rs = statement.executeQuery( "select t from foo") ){
if (rs.next()) {
var result = rs.getObject(1, OffsetDateTime.class);
System.out.println("Result: " + result);
}
}
}
}
}
}
```
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3943: OffsetDateTime with TIMESTAMP WITH TIME ZONE column always return UTC as ZoneOffset.
@ 2026-02-23 09:01 "vlsi (@vlsi)" <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: vlsi (@vlsi) @ 2026-02-23 09:01 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
This duplicates https://github.com/pgjdbc/pgjdbc/issues/1324
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-02-23 09:01 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-23 06:04 [pgjdbc/pgjdbc] issue #3943: OffsetDateTime with TIMESTAMP WITH TIME ZONE column always return UTC as ZoneOffset. "hgschmie (@hgschmie)" <[email protected]>
2026-02-23 09:01 Re: [pgjdbc/pgjdbc] issue #3943: OffsetDateTime with TIMESTAMP WITH TIME ZONE column always return UTC as ZoneOffset. "vlsi (@vlsi)" <[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