pgjdbc: Inconsistent behaviour between ResultSet.getString() and ResultSet.getObject() when fetching TIMESTAMP WITH TIME ZONE values

I’m submitting a …

bug report
feature request

Describe the issue

Consider this logic:

try (Connection connection = DriverManager.getConnection(
    "jdbc:postgresql://localhost:5432/postgres",
    "postgres",
    "test")
) {

    try (ResultSet rs = connection.createStatement().executeQuery("select timestamp with time zone '2000-01-01 00:00:00Z'")) {
        rs.next();

        System.out.println(rs.getString(1));
        System.out.println(rs.getObject(1, OffsetDateTime.class));
    }
}

The above prints:

2000-01-01 01:00:00+01
2000-01-01T00:00Z

This difference of behaviour seems unfortunate. Even if the JDBC spec doesn’t seem to be specific about this behaviour, and even if the java.time.Instant representation is the same for both, I’d favour consistency in this case over the current behaviour. I think that the local time zone version should be preferred. For instance, psql also returns the timestamp in local time zone:

postgres=# select timestamp with time zone '2000-01-01 00:00:00Z';
      timestamptz
------------------------
 2000-01-01 01:00:00+01
(1 Zeile)

Java Version

C:\Program Files\PostgreSQL\10\bin>java -version
openjdk version "11" 2018-09-25
OpenJDK Runtime Environment 18.9 (build 11+28)
OpenJDK 64-Bit Server VM 18.9 (build 11+28, mixed mode)

OS Version

C:\Program Files\PostgreSQL\10\bin>ver

Microsoft Windows [Version 10.0.17134.345]

PostgreSQL Version

postgres=# select version();
                          version
------------------------------------------------------------
 PostgreSQL 10.0, compiled by Visual C++ build 1800, 64-bit
(1 Zeile)

About this issue

Original URL
State: open
Created 6 years ago
Comments: 21 (12 by maintainers)

Most upvoted comments

jOOQ/jOOQ#7986 seems to suggest pgjdbc translates this “wrong”. However the basic problem is that OffsetDateTime reflects data (specifically an offset) which postgresql simply does not have. The postgresql data type is most closely aligned to an Instant or java.sql.Timestamp.

I’m not sure why ODT is so much different from both java.sql.Timestamp or String. Both represent the exact Instant, and both represent it in the local time zone, just like the psql example has shown.

Getting it in any other representation (including String) requires additional data, such as time zone, to be provided or guessed.

Again, from https://www.postgresql.org/docs/current/static/datatype-datetime.html

It says “When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct”. I don’t see why ODT needs to be different, here.

Yep, the type I actually want out of a timestamp with time zone is Instant, not ODT.

Supporting Instant in ResultSet.getObject(), PreparedStatement.setObject(), CallableStatement.getObject() seems very useful and not contradictory to my claims here.

lukaseder on Oct 28, 2018

I do not think consistency between getString and getObject(Class).toString() is possible when multiple different object classes are supported and those classes have different toString implementations. For example getObject(TimeStamp.class).toString() will return yet another different value.

On Fri, Oct 26, 2018 at 9:53 AM Lukas Eder notifications@github.com wrote:

I personally don’t really care which offset is being returned, as long as it is consistent and thus predictable.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/pgjdbc/pgjdbc/issues/1324#issuecomment-433435346, or mute the thread https://github.com/notifications/unsubscribe-auth/AC26bW4EdjtIvgd-zz5dZkJqwZirUSXNks5uoyHbgaJpZM4X8H6z .

bokken on Oct 26, 2018