Multiple longstanding bugs in java.sql.{Date,Time,Timestamp} conversions

[jcflack]

Analysis for issue #199 reveals that there are several other issues involving the pre-(JSR 310, JDBC 4.2) date, time, timestamp conversions to and from the java.sql.{Date,Time,Timestamp} classes. Unlike issue #199, these are not regressions in 1.5.1, but older historical behavior; they will be left unchanged for 1.5.2 and fixed in some later release.

Unsurprisingly, they all have to do with the timezone manipulations that the driver has to do, even when the PG type involves no timezone, simply because the java.sql classes inherit from java.util.Date, which assumes knowledge of a timezone.

Plug: The Java 8, JDBC 4.2 / JSR 310 java.time types are free of these issues

These bugs in the legacy java.sql mappings can be fixed, and will be in a future PL/Java release, but this is a good time for another plug that PL/Java 1.5.1 supports the JDBC 4.2 mappings to Java 8's JSR 310 java.time types, which already are free of these issues, and migration to them is encouraged wherever practical.

The following issues are with the legacy mappings to java.sql.{Date,Time,Timestamp} classes.

Errors when PG and Java timezones do not match

For the PG types without timezone, the driver has to apply an offset corresponding to the local timezone. Historically, it has been using PG's local timezone. This gives correct results only when the PG timezone setting and the Java runtime's default timezone happen to be the same. That isn't guaranteed: the PG timezone can be set per session, possibly to match the location of a connected client, and the Java timezone typically defaults from the server OS settings (unless it is explicitly set in Java code, or with -Duser.timezone=... in pljava.vmoptions).

A future PL/Java version might automatically set user.timezone to match the PG session timezone (if it is not explicitly set in pljava.vmoptions), but the correctness of these conversions should not be left to depend on that. They are already querying PG for its timezone offset; they should simply be querying Java for its offset instead. This is explicit in the API spec for java.sql.Date:

the driver will set the time components to the time in the default time zone (the time zone of the Java virtual machine running the application) that corresponds to zero GMT.

(emphasis mine). Although there are no similarly explicit statements in the API docs for Time and Timestamp, it turns out that the principle holds. Using Java's timezone consistently for exactly the adjustments that are currently using PG's timezone will produce correct mappings whether or not the PG and Java zones happen to match.

Out-of-range values passed to java.sql.Time constructor

The java.sql.Time class is meant to represent a time of day only, and per spec is initialized the same as a java.util.Date representing that time of day on 1 January 1970 in UTC. The PG driver will currently pass a value representing the current date. The Java class does not (at present!) detect or reject that, and its toString method produces the expected value (the date part is simply ignored), but any Java code that looked at the underlying milliseconds value would find it out of the expected range.

Such Time objects round-trip back to PG successfully (but for the next issue), because PL/Java also does not (at present!) detect or reject an out-of-range value, but simply mods out any date part.

However:

Sign problems in modular arithmetic, java.sql.Time to PG time with time zone

This is reminiscent of the 'does % round or floor?' issue in #155 (C before C99 doesn't specify!):

\c
SET pljava.vmoptions TO '-Duser.timezone=GMT-1';
SET TIME ZONE 'FOO+1'; -- PG's syntax for the same offset; note sign interpretation flipped
SELECT *
 FROM
  (SELECT '23:00:00-1'::timetz) AS p,
  roundtrip(p) AS (class text, tostring text, roundtripped timetz);
   timetz    |     class     | tostring | roundtripped 
-------------+---------------+----------+--------------
 23:00:00-01 | java.sql.Time | 23:00:00 | -1:00:00-01

(the example ensures PG and Java are set to the same timezone, a simple one-hour offset west of Greenwhich. The problem can be observed in other timezones, but this makes a simple reproducible test.)

Bogus results for timestamp without time zone near start and end of summer time

\c
SET pljava.vmoptions TO '-Duser.timezone=Europe/Prague';
SET TIME ZONE 'Europe/Prague';
SELECT *
  FROM
    (VALUES
      ('2018-03-25 00:00:00'::timestamp),
      ('2018-03-25 01:00:00'),
      ('2018-03-25 02:00:00'),
      ('2018-03-25 03:00:00'),
      ('2018-10-28 00:00:00'),
      ('2018-10-28 01:00:00'),
      ('2018-10-28 02:00:00')
    ) AS p,
    roundtrip(p) AS (class text, tostring text, roundtripped timestamp);
       column1       |       class        |	  tostring	  |    roundtripped     
---------------------+--------------------+-----------------------+---------------------
 2018-03-25 00:00:00 | java.sql.Timestamp | 2018-03-25 00:00:00.0 | 2018-03-25 00:00:00
 2018-03-25 01:00:00 | java.sql.Timestamp | 2018-03-25 00:00:00.0 | 2018-03-25 00:00:00
 2018-03-25 02:00:00 | java.sql.Timestamp | 2018-03-25 01:00:00.0 | 2018-03-25 01:00:00
 2018-03-25 03:00:00 | java.sql.Timestamp | 2018-03-25 03:00:00.0 | 2018-03-25 03:00:00
 2018-10-28 00:00:00 | java.sql.Timestamp | 2018-10-28 00:00:00.0 | 2018-10-28 00:00:00
 2018-10-28 01:00:00 | java.sql.Timestamp | 2018-10-28 02:00:00.0 | 2018-10-28 02:00:00
 2018-10-28 02:00:00 | java.sql.Timestamp | 2018-10-28 02:00:00.0 | 2018-10-28 02:00:00

In this example, midnight before the start of summer time maps to Java correctly and roundtrips correctly. 01:00 fails to map or roundtrip correctly, despite an hour still remaining before anything interesting should happen. 02:00 is technically an invalid local timestamp (part of the hour that doesn't exist from 02:00 to 03:00). It could either be mapped ahead to 03:00 with or without a warning, or flagged as an error (Java 8's JSR 310 types offer those behaviors). To map it an hour earlier, as seen here, is in no interpretation correct! By 03:00, the bogosity is passed, and matching Java and roundtrip values are again produced.

At the end of summer time, midnight again is handled correctly, and correctness returns for 02:00 onward, but for the hour starting at 01:00, the Java and roundtrip values are incorrect. (There is an ambiguity at the end of summer time such that local times in the hour starting at 02:00 could represent two different actual instants, but that ends up being mostly transparent; unlike the start of summer time, this is an overlap, not a gap where some times are invalid. But the behavior here is messing up the hour preceding the overlap, which is plainly wrong.)

The choice of Europe/Prague as the timezone is not necessary to see the problem, but simply used to make this demonstration reproducible. The details of the errors vary if the zone is west rather than east of Greenwich, and the errors' duration varies with the magnitude of the offset: Prague is only one hour off GMT, and for other zones, far more than one or two hours can be in error on the morning of a transition.