TimestampUtils.toJavaSecs / toPgSecs seem dubious - Mailing list pgsql-jdbc

Hi,

I'm kind of auditing PL/Java's date/time mapping code, and ran into
a spot where it and pgJDBC start giving different results.

October 1582 was a weird month. If you were in a European Catholic
country, you switched to Pope Gregory's calendar then, and your day
after the 4th was the 15th. Other countries switched at way different
times, some not until the 20th century.

ISO 8601, and therefore SQL, PostgreSQL, and the Java 8 java.time types,
all use Gregory's calendar proleptically, that is, out forever in both
directions, even into the past before anybody was using it in real life.
So they should render dates in a steady sequence without goofy jumps.

The older java.util.Date class, and therefore the java.sql.Date/Time/
Timestamp classes based on it, do a ten-day jump that month, just like
you would have if you lived in a European Catholic country.

So if you generate a little sequence of PostgreSQL timestamps from
October 16 backwards to September 30 that year, and you retrieve them
in PL/Java as java.sql.Timestamp and as java.time.LocalDateTime, and
in pgJDBC the same way (using binary protocol), you get this:

  PG              PL/Java                 pgJDBC
1582-        java.sql  java.time    java.sql  java.time
 10-16          16        16           16        16
    15          15        15           15        15
    14           4        14           24        24
    13           3        13           23        23
    12           2        12           22        22
    11           1        11           21        21
    10        9-30        10           20        20
     9          29         9           19        19
     8          28         8           18        18
     7          27         7           17        17
     6          26         6           16        16
     5          25         5           15        15
     4          24         4            4        14
     3          23         3            3        13
     2          22         2            2        12
     1          21         1            1        11
  9-30          20      9-30         9-30        10


PL/Java, which does no special munging on the milliseconds value
it feeds to Java, produces a sequence of java.time values that has
no break and matches the PostgreSQL values, as they should,
being their proleptic little selves. It gets java.sql values
that do the thing they do, showing you what you'd have seen as
a European Catholic.

pgJDBC's toJavaSecs() and toPgSecs() methods contain some dubious
adjustments, apparently there in an attempt to make the java.sql
mapping less weird. But it really succeeds only in smearing the weird
around so it's not where it belongs, and some even gets on the java.time
mapping, which ought to be free of weird.

Both mappings repeat the dates 10-24 down to 10-15, and then, continuing
backward from there, it's the java.sql value that matches PostgreSQL,
and the java.time value that doesn't (!), and really nowhere
earlier than 10-15 does either one produce the value it would be
expected to.

I don't know what to suggest doing about it, as I'm sure it's been
like that a long time, but it seemed worth pointing out. My inclination
would be to lose the special adjustments in toJavaSecs/toPgSecs, and
just let both the java.sql and java.time classes behave the way they do.

Regards,
-Chap