pgjdbc/pgjdbc GitHub issues and pull requests (mirror)  

help / color / mirror / Atom feed

From: hgschmie (@hgschmie) <[email protected]>
To: pgjdbc/pgjdbc <[email protected]>
Subject: [pgjdbc/pgjdbc] issue #3943: OffsetDateTime with TIMESTAMP WITH TIME ZONE column always return UTC as ZoneOffset.
Date: Mon, 23 Feb 2026 06:04:19 +0000
Message-ID: <[email protected]> (raw)

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);
                    }
                }
            }
        }
    }
}
```

view thread (2+ 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 #3943: OffsetDateTime with TIMESTAMP WITH TIME ZONE column always return UTC as ZoneOffset.
  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