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.

[martinwyser]

@davecramer Thanks for the hint. I am already doing that. In this case, the combination of OffsetDateTime and timestamp was chosen to avoid conversions.

I have looked at this in more detail, and the difference between 42.2.6 and ...7 boils
down to two places. When the same timestamp without timezone value is retrieved, the
same value is received on the wire for both jdbc library versions, but it is handled
differently.

In 42.2.6, the String is converted into a Timestamp using a Calendar for the local timezone (+1H).

PgResultSet.java#L566:

cal = getDefaultCalendar();

and then on line 600:

return connection.getTimestampUtils().toTimestamp(cal, string);

This is then converted into an OffsetDateTime as a UTC time in PgResultSet.java#L3372:

OffsetDateTime offsetDateTime = OffsetDateTime.ofInstant(timestampValue.toInstant(), ZoneOffset.UTC);

So, the string received from the server is taken to be in time zone of the client, which happens in getDefaultCalendar();

In 42.2.7, the string is parsed with an offset of 0, and converted directly into an OffsetDateTime TimestampUtils.java#506

  offsetSeconds = 0;

and then on line 512:

 OffsetDateTime result = OffsetDateTime.of(ts.year, ts.month, ts.day, ts.hour, ts.minute, ts.second, ts.nanos, zoneOffset)

This is a breaking change, and I think it is not ok. There is also no use supporting OffsetDateTime for timestamp fields if it does not work. Also, the fact whether there is conversion to or from a timezone should depend on the type of the database field, not on the Java type.

Anyway, thanks again for the help! I appreciate your work a lot!

[vlsi]

There's an option to throw an error if users try accessing timestamp via OffsetDateTime. We definitely know the server did not return timezone, and we definitely know the user requested "with timezone". I believe the best option is to throw an error (with a kill switch so the users could temporary silence it)

[davecramer]

I suppose, however, my guess is this would break a lot of code. I don't think we could make this the default.

[rbygrave]

throw an error if users try accessing timestamp via OffsetDateTime

FWIW This makes sense to me. My gut feel is that making the code it did break forced to opt in explicitly to allow this initially might sound harsh could also might be considered reasonable from a "safety" perspective in this case.

[davecramer]

If we did this, it would have to be in a new major version release.

[martinwyser]

Hi guys, thank a lot for your hints and opinions, I appreciate it very much. @davecramer I think breaking existing code is really an argument. As for the timezone to be "guessed": what does the server send for a "timestamp without"? It stores the time in UTC, and probably sends an UTC string. If an OffsetDateTime is requested, the library could use "any time zone", but it should represent the same instant. For "any time zone": of course there are natural choices like UTC or that of the JVM, or my preferences, but what matters is that it is known.

Currently there is also an asymmetry: on writing an OffsetDateTime value, its time zone is ignored, and that of the JVM is used. That changes the time! On reading, the JVM time zone is ignored >= 42.2.7. For 42.2.6, the JVM timezone was used for reading, which balanced the behaviour on writing.

But enough of the complaining: my timestamp fields are all with tz, except one. I can manage this. But it was quite a surprise!

So, thanks again, guys.

[davecramer]

@martinwyser

Hi guys, thank a lot for your hints and opinions, I appreciate it very much. @davecramer I think breaking existing code is really an argument. As for the timezone to be "guessed": what does the server send for a "timestamp without"? It stores the time in UTC, and probably sends an UTC string. If an OffsetDateTime is requested, the library could use "any time zone", but it should represent the same instant. For "any time zone": of course there are natural choices like UTC or that of the JVM, or my preferences, but what matters is that it is known.

Currently there is also an asymmetry: on writing an OffsetDateTime value, its time zone is ignored, and that of the JVM is used. That changes the time! On reading, the JVM time zone is ignored >= 42.2.7. For 42.2.6, the JVM timezone was used for reading, which balanced the behaviour on writing.

But enough of the complaining: my timestamp fields are all with tz, except one. I can manage this. But it was quite a surprise!

So, thanks again, guys.

Hi guys, thank a lot for your hints and opinions, I appreciate it very much. @davecramer I think breaking existing code is really an argument. As for the timezone to be "guessed": what does the server send for a "timestamp without"? It stores the time in UTC, and probably sends an UTC string.
I think this the challenge.
1/ The server does not store the time in UTC, it stores whatever you send it.
2/ No timezone is sent whatsoever. See https://github.com/postgres/postgres/blob/98b4f53d156efe09294d6e1d62a5b52f544eee29/src/backend/utils/adt/timestamp.c#L232 and subsequent calls to https://github.com/postgres/postgres/blob/98b4f53d156efe09294d6e1d62a5b52f544eee29/src/backend/utils/adt/timestamp.c#L1901

What happens with timestamp with timezone is that the timestamp is stored as UTC and when it is sent back it is sent back at the session timezone

If an OffsetDateTime is requested, the library could use "any time zone", but it should represent the same instant. For "any time zone": of course there are natural choices like UTC or that of the JVM, or my preferences, but what matters is that it is known.

But enough of the complaining: my timestamp fields are all with tz, except one. I can manage this. But it was quite a surprise!

Glad to hear!
Dave