Search code examples
javapostgresqltimezonetimestamp-with-timezone

Use java.util.Date to query column with TIMESTAMPTZ

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'
Solution

  • 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