1

I'm confused about using PostgreSQL's TIMESTAMPTZ type with the official JDBC driver.

Correct me if I'm wrong, but PostgreSQL and Java store TIMESTAMTZ and java.util.Date identically: as the number of the millis from the Begin of Unix, defined as 1970-01-01 00:00:00 UTC.

Therefore, technically, we are operating on the same Long value, and we should expect no problems.

However, we've got quite a lot of problems with the code doing a lot of transformations in one or other direction, that were replaced by even more complex transformations, which happen to work afterwards. The end result was a bit similar to https://stackoverflow.com/a/6627999/5479362, with transforming to UTC in both directions. Developing under Windows, where changing timezone is blocked, makes things not easier to debug.

If I have a PostgreSQL table with the column:

last_modified TIMESTAMP WITH TIMEZONE

and I want to query it with the Date instance:

Date modifiedAfter = new SimpleDateFormat("yyyy-MM-dd hh:MM:ss").parse("2021-06-11 15:20:00");
PreparedStatement ps = conn.prepareStatement("Select * from mytable where last_modified > ?");
ps.setDate(1, modifiedAfter);

Than it will work correctly, because PostgreSQL JDBC driver will translate the java Date to the UTC Long, using the Locale of my java program, and the the DB server will use its own Locale when that date will be queried?

Or those conversions are not handled correctly if the Locale of the server differs from the Locale of the client, therefore I need to operate only on UTC dates?

The issue that I have with the answer linked above is, that the OP has claimed that it 'works for him', which means, that the Java always reads what was written, but not necessarily, that the value is stored correctly in the DB, so that other clients would read what is expected.

Is the way above the correct way of handling TIMESTAMTZ to assure, that both Java code, and the output of the query in PSQL console would give the consistent result? If not, what's the correct solution?

I expect, that when I write '2021-11-06 15:00:00' with my Java program running with TimeZone +4:

  • Java Program with TimeZone +2 will get '2021-11-06 13:00:00'
  • PSQL Client with TimeZone +1 will get '2021-11-06 12:00:00'
  • intern, it will be '2021-11-06 11:00:00'
9ilsdx 9rvj 0lo
  • 7,955
  • 10
  • 38
  • 77
  • 1
    Sun, Oracle, and the JCP community gave up on the terrible `Date`, `Calendar`, `Timestamp`, and `SimpleDateFormat` classes when they unanimously adopted JSR 310 defining the *java.time* classes. I suggest you move on to *java.time* as well. – Basil Bourque Jun 11 '21 at 15:15

1 Answers1

4

Don't use java.util.Date, use java.time.OffsetDateTime

OffsetDateTime modifiedAfter = OffsetDateTime.of(2021, 6, 11, 15, 20, 0, 0, ZoneOffset.UTC);
ps.setObject(1, modifiedAfter);

Do the same when reading the value:

ResultSet rs = statement.executeQuery(...);
while (rs.next()) {
  OffsetDateTime odt = rs.getObject(1, OffsetDateTime.class);
  ....
}

If you don't care about time zones at all, and are sure that everything will always be specified with the same time zone (e.g. UTC), then use timezone as the column data type in Postgres.

Then use LocalDateTime instead of OffsetDateTime in Java

  • Is it necessary to use ZoneOffset.UTC in order to get JDBC driver to work as expected? I've just realised that I'm lacking the understanding WHAT is actually sent to the DB – 9ilsdx 9rvj 0lo Jun 11 '21 at 13:40
  • The value sent to the database will be adjusted to the time zone specified for the current connection (`show timezone;` will tell you which one it is) and is initialized by the driver to the client computer's time zone –  Jun 11 '21 at 13:45
  • In that case, shouldn't java.util.Date work the same? Because technically, java.util.Date is the wrapper along the long representing the number of millis since A.U. in UTC. Theoretically, nothing to convert. I thing that all my issues come from the lack of understanding what happens between the lines: https://stackoverflow.com/questions/67938171/how-date-types-are-handled-by-the-jdbc-driver – 9ilsdx 9rvj 0lo Jun 11 '21 at 13:51
  • 2
    No, `java.util.Date` has absolutely no notion of time zones. `java.util.Date` and the corresponding `java.sql.Timestamp` and `java.sql.Date` classes should considered deprecated. Stop using them. –  Jun 11 '21 at 13:53
  • yes, but java.util.Date doesn't differ in that matter from timestamptz. Expression "select '2021-06-11 14:00+02'::timestamptz = '2021-06-11 16:00+04'::timestamptz;" returns 'true'. Therefore I'm surprising that that type produces issues. – 9ilsdx 9rvj 0lo Jun 11 '21 at 13:59
  • I see that OffsetDateTime is used internally in the driver, so I'll switch my code to use this – 9ilsdx 9rvj 0lo Jun 11 '21 at 14:40