Problems with Java OffsetDateTime

[martinwyser]

Hi everybody

I am putting OffsetDateTime values into timestamp columns, and values I read back are different. The behaviour depends on the driver version. I have an example below.
Upto to 42.2.6, the behaviour is consistent, I get back what I write. 42.2.7, 42.3...., 42.4... get values back with an offset which depends on the current systems timezone. The behaviour does not depend on the server version, I tested with 13.7 and also some 14 and 15 versions.
Here is my "test". It assumes a postgres on localhost:5555:

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.time.OffsetDateTime;
import java.time.ZoneOffset;
import java.time.temporal.ChronoUnit;

/**
 * Put the jdbc driver into the current directory, along with two versions of pgJDBC,
 * and run these commands:
 * <pre>
 *   java -cp .;postgresql-42.2.6.jar TestJdbcOffsetDateTime.java
 *   java -cp .;postgresql-42.2.7.jar TestJdbcOffsetDateTime.java
 * </pre>
 * For 42.2.6 and earlier, the result will have 0 seconds difference. For 42.2.7,
 * the diff is usually != 0, depending on the timezone.
 * For March 1, 2000, the diff here in timezone MET-DST is 3600.
 * For April 1, 2000, the diff is 7200!
 */
public class TestJdbcOffsetDateTime {

public static void main(String[] args) throws SQLException {
  Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5555/postgres", "postgres", null);
  conn.setAutoCommit(false);

  Statement stmt = conn.createStatement();
  stmt.execute("""
    drop table if exists x;
    create table x (
      x_id serial primary key, t timestamp not null, ts timestamptz not null default current_timestamp
    )""");
  stmt.execute("delete from x");
  
  OffsetDateTime t = OffsetDateTime.of(2000, 3, 1, 0, 0, 0, 0, ZoneOffset.UTC);
  PreparedStatement ps = conn.prepareStatement("insert into x (t) values (?)");
  ps.setObject(1, t);
  ps.executeUpdate();
  conn.commit();
  
  ResultSet rs = stmt.executeQuery("select t from x");
  if (!rs.next()) {
    System.out.println("no rows found");
  } else {
    OffsetDateTime u = rs.getObject(1, OffsetDateTime.class);
    System.out.printf("wrote t=%s, read u=%s, diff %d seconds\n", t, u, ChronoUnit.SECONDS.between(t, u));
  }
  conn.commit();
}
}

Here is sample output:

C:\Users\...>java -cp postgresql-42.2.6.jar TestJdbcOffsetDateTime.java
wrote t=2000-03-01T00:00Z, read u=2000-03-01T00:00Z, diff 0 seconds

C:\Users\...>java -cp postgresql-42.2.7.jar TestJdbcOffsetDateTime.java
wrote t=2000-03-01T00:00Z, read u=2000-03-01T01:00Z, diff 3600 seconds
```

I am quite puzzled by this. Is there a suggestion on how to handle this, or advice about the change?

I am also surprised that the values inserted by 42.2.6 seem to be wrong, but reading back inverts the time-shift. However, 42.2.7 and later have asymmetric behaviour, which caught the attention of one of our tests, and clearly not ok.

[davecramer]

@martinwyser thanks for the report, I can look into this tomorrow.
Are you using 42.2.x for a reason (not relevant to the issue but I'm curious)?

[martinwyser]

@davecramer thank for your answer! No particular reason, it is just what we chose, maybe two years ago, at the start of the project. I sometimes avoided versions with a dependency on the checkers framework, but not really a strong preference.

[davecramer]

@martinwyser OK, REL42.2.6...REL42.2.7#diff-21e3023c84d9d5fb4f5f04678a094fce6ab5af36a5e73fdfaf495646f3b720d5R485 we added toOffsetDateTime in this PR https://github.com/pgjdbc/pgjdbc/pull/1539/files

This is technically correct. If you change your code to use LocalDateTime everything works as expected, or if you change your data type to timestamptz it also works as expected. This is consistent with the mapping of

LocalDateTime <-> Timestamp without time zone
and
OffsetDateTime <-> Timestamp with time zone

Since LocalDateTime does not have a time zone and OffsetDateTime does.

Regards

Dave

[martinwyser]

Thank you very much for your hints, @davecramer !
Using LocalDateTime creates uncertaintity about the time zone the value is to be understood in.
That is why I would prefer to have a zone / offset in every date-time value.

I have meanwhile investigated this further, and found out the value in the database is already
incorrect after the insert, on my machine. It came out that the timezone of the session was set
to CET (from the OS on my machine!). I remember that earlier versions of the driver set this to UTC,
and did their own timezone magic. This no longer seems to be the case.
So, I used "set timezone to 'UTC'", and lo and behold, everything works as expected.

However, I am not very fond of this fix either. I'll inform myself about the possibilities
of configuring this. Any hints are welcome, of course.

The good news is that this does not affect our production system, as they have UTC. Only
our test systems have CET.

[davecramer]

Thank you very much for your hints, @davecramer ! Using LocalDateTime creates uncertaintity about the time zone the value is to be understood in. That is why I would prefer to have a zone / offset in every date-time value.

But you are using Timestamp without Time Zone as the data type.

I have meanwhile investigated this further, and found out the value in the database is already incorrect after the insert, on my machine. It came out that the timezone of the session was set to CET (from the OS on my machine!). I remember that earlier versions of the driver set this to UTC, and did their own timezone magic. This no longer seems to be the case. So, I used "set timezone to 'UTC'", and lo and behold, everything works as expected.
Right since the data that we send would not be adjusted for time zone.

However, I am not very fond of this fix either. I'll inform myself about the possibilities of configuring this. Any hints are welcome, of course.

I think you need to either change your data type to timestamp with timezone or use LocalDateTime

You should know that even with timestamptz postgres does not store the timezone, the data is stored at UTC

The good news is that this does not affect our production system, as they have UTC. Only our test systems have CET.

[martinwyser]

@davecramer Sorry for not making that clearer: I know that the timestamps are stored without timezone, which is ok, we do not have to keep the timezone. And because all timestamps are in UTC, there can be no confusion. But in the Java process we get data from different timezones, so we get OffsetDateTime. When we return data, we also have to provide a timezone. Everything outside of the database has an explicit timezone attached to it.

Inside the database, no explicit timezone is required, because everything is in UTC, and we don't need to keep the timezone either.

[davecramer]

In that case you would be much better off using timestamptz inside the database since the driver will send the data with the timezone and postgres stores it @ UTC, when it returns it postgres returns it at whatever TZ the client is in. All of this conversion is done automatically for you.