The first mappings to be specified in JDBC used the JDBC-specific classes
java.sql.Date, java.sql.Time, and java.sql.Timestamp, all of which
are based on java.util.Date (but only as an implementation detail; they
should always be treated as their own types and not as instances of
java.util.Date).
PL/Java function parameters and returns can be declared in Java to have those
types, objects of those types can be passed to PreparedStatement.setObject,
ResultSet.updateObject, and SQLOutput.writeObject methods, as well as to
the methods that are specific to those types. The JDBC getObject and
readObject methods that do not take a Class<?> parameter will return
objects of those types when retrieving PostgreSQL date or time values.
Those classes have never been a good representation for PostgreSQL date/time
values, because they are based on java.util.Date, which implies knowledge of
a time zone, even when they are used to represent PostgreSQL values with no time
zone at all. For all of these conversions but one, PL/Java must do time zone
computations, with the one exception being, unintuitively, timestamp with time zone. The conversions of non-zoned values involve a hidden dependency on the
PostgreSQL session's current setting of TimeZone, which can vary from session
to session at the connecting client's preference.
There are known issues of long standing in PL/Java's conversions to and from these types, detailed in issue #200. While these particular issues are expected to be fixed in a future PL/Java release, the Java 8 / JDBC 4.2 mappings described next are the strongly-recommended alternative to the legacy mappings, avoiding these issues entirely.
Java 8 introduced the much improved set of date/time classes in the java.time
package specified by JSR 310. JDBC 4.2 (the version in Java 8)
allows those as alternate Java class mappings of the SQL types date,
time (with and without timezone), and timestamp (with/without timezone).
These new types are a much better fit to the corresponding PostgreSQL types than
the original JDBC java.sql Date/Time/Timestamp classes.
To avoid a breaking change, JDBC 4.2 does not modify what any of the
pre-existing JDBC API does by default. The getDate, getTime, and
getTimestamp methods on a ResultSet still return the same java.sql types,
and so does getObject in the form that does not specify a class. Instead, the
update takes advantage of the general purpose ResultSet.getObject methods that
take a Class<?> parameter (added in JDBC 4.1), and likewise the
SQLInput.readObject method with a Class<?> parameter (overlooked in 4.1 but
added in 4.2), so a caller can request a java.time class by passing the right
Class:
| PostgreSQL type | Pass to getObject/readObject |
|---|---|
date |
java.time.LocalDate.class |
time without time zone |
java.time.LocalTime.class |
time with time zone |
java.time.OffsetTime.class |
timestamp without time zone |
java.time.LocalDateTime.class |
timestamp with time zone |
java.time.OffsetDateTime.class |
The java.time types can also be used as parameter and return types of PL/Java
functions without special effort (the generated function declarations will make
the right conversions happen), and passed to the setter methods of prepared
statements, writable result sets (for triggers or composite-returning
functions), and SQLOutput for UDTs.
Conversions to and from these types never involve the PostgreSQL session time zone, which can vary from session to session. Any code developed for PL/Java and Java 8 or newer is strongly encouraged to use these types for date/time manipulations, for their much better fit to the PostgreSQL types.
PostgreSQL accepts 24:00:00.000000 as a valid time, while a day for
LocalTime or OffsetTime maxes out at the preceding nanosecond. That is
still a distinguishable value (as the PostgreSQL resolution is only to
microseconds), so the PostgreSQL 24 value is bidirectionally mapped to that.
When a time with time zone is mapped to a java.time.OffsetTime, the Java
value will have a zone offset equal to the one assigned to the value in
PostgreSQL, and so in the reverse direction.
When a timestamp with time zone is mapped to a java.time.OffsetDateTime,
the Java value will always have a zone offset of zero (UTC). When an
OffsetDateTime created in Java is mapped to a PostgreSQL
timestamp with time zone, if its offset is not zero, the value adjusted to UTC
is used.
These different behaviors accurately reflect how PostgreSQL treats the two types differently.
PostgreSQL allows date and timestamp (with or without time zone) values of
infinity and -infinity.
There is no such notion in the corresponding Java classes (the original JDBC
ones or the JDBC 4.2 / JSR 310 ones), but PL/Java will map those PostgreSQL
values repeatably to certain values of the Java classes, and will map Java
objects with those exact values back to PostgreSQL infinity or -infinity
on the return trip. Java code that needs to recognize those values could do
an initial query returning infinity and -infinity and save the resulting
Java values to compare others against. It must compare with equals(); it
cannot assume that the mapping will produce the very same Java objects
repeatedly, but only objects with equal values.
When dates and timestamps are mapped to the java.time classes,
the mapping will have
the useful property that -infinity really is earlier than other
PostgreSQL-representable values, and infinity really is later. That does not
hold under the old java.sql.Timestamp mapping, where both values will be
distant from the present but not further specified.
integer_datetimesIn PostgreSQL builds with integer_datetimes as off (a configuration that is
non-default since PostgreSQL 8.4, and impossible since PG 10), an error results
if a timestamp being converted to Java has either infinite value. As uses of
infinite timestamps are probably rare and the configuration is long out of use,
there is no plan to lift this limitation unless an issue is opened to address a
practical need.