Module org.postgresql.pljava
Package org.postgresql.pljava.adt

Interface Timespan.Interval<T>

All Superinterfaces:
Adapter.Contract<T>, Adapter.Contract.Scalar<T>
Enclosing interface:
Timespan
Functional Interface:
This is a functional interface and can therefore be used as the assignment target for a lambda expression or method reference.
@FunctionalInterface public static interface Timespan.Interval<T> extends Adapter.Contract.Scalar<T>
The INTERVAL type's PostgreSQL semantics: separate microseconds, days, and months components, independently signed.

A type modifier can specify field-presence bits, and precision (number of seconds digits to the right of the decimal point). An empty fields set indicates that fields were not specified.

Infinitely negative or positive intervals

Starting with PostgreSQL 17, intervals whose three components are (Long.MIN_VALUE, Integer.MIN_VALUE, Integer.MIN_VALUE) or (Long.MAX_VALUE, Integer.MAX_VALUE, Integer.MAX_VALUE) have the semantics of infinitely negative or positive intervals, respectively. In PostgreSQL versions before 17, they are simply the most negative or positive representable finite intervals.

Why no reference implementation?

The types in the Datetime interface come with reference implementations returning Java's JSR310 java.time types.

For PostgreSQL INTERVAL, there are two candidate JSR310 concrete types, Period and Duration, each of which would be appropriate for a different subset of PostgreSQL INTERVAL values.

Period is appropriate for the months and days components. A Period treats the length of a day as subject to daylight adjustments following time zone rules, as does PostgreSQL.

Duration is suitable for the sub-day components. It also allows access to a "day" field, but treats that field, unlike PostgreSQL, as having invariant 24-hour width.

Both share the superinterface TemporalAmount. That interface itself is described as "a framework-level interface that should not be widely used in application code", recommending instead that new concrete types can be created that implement it.

PostgreSQL's INTERVAL could be represented by a concrete type that implements TemporalAmount or, preferably (because its days and months components are subject to rules of a chronology), its subinterface ChronoPeriod. The most natural such implementation would have getUnits return MONTHS, DAYS, and MICROS, except for instances representing the infinitely negative or positive intervals and using the unit FOREVER with a negative or positive value.

In the datatype library that comes with the PGJDBC-NG driver, there is a class com.impossibl.postgres.api.data.Interval that does implement TemporalAmount (but not ChronoPeriod) and internally segregates the PostgreSQL INTERVAL components into a Period and a Duration. An application with that library available could use an implementation of this functional interface that would return instances of that class. As of PGJDBC-NG 0.8.9, the class does not seem to have a representation for the PostgreSQL 17 infinite intervals. Its getUnits method returns a longer list of units than needed to naturally represent the PostgreSQL type.

The PGJDBC driver includes the org.postgresql.util.PGInterval class for the same purpose; that one does not derive from any JSR310 type. As of PGJDBC 42.7.5, it does not explicitly represent infinite intervals, and also has an internal state split into more units than the natural representation would require.

SQL/XML specifies how to map SQL INTERVAL types and values to the XML Schema types xs:yearMonthDuration and xs:dayTimeDuration, which were added in XML Schema 1.1 as distinct subtypes of the broader xs:duration type from XML Schema 1.0. That Schema 1.0 supertype has a corresponding class in the standard Java library, javax.xml.datatype.Duration, so an implementation of this functional interface to return that type would also be easy. It would not, however, represent PostgreSQL 17 infinitely negative or positive intervals.

These XML Schema types do not perfectly align with the PostgreSQL INTERVAL type, because they group the day with the sub-day components and treat it as having invariant width. (The only time zone designations supported in XML Schema are fixed offsets, for which no daylight rules apply). The XML Schema types allow one overall sign, positive or negative, but do not allow the individual components to have signs that differ, as PostgreSQL does.

Java's JSR310 types can be used with equal convenience in the PostgreSQL way (by assigning days to the Period and the smaller components to the Duration) or in the XML Schema way (by storing days in the Duration along with the smaller components), but of course those choices have different implications.

A related consideration is, in a scheme like SQL/XML's where the SQL INTERVAL can be mapped to a choice of types, whether that choice is made statically (i.e. by looking at the declared type modifier such as YEAR TO MONTH or HOUR TO SECOND for a column) or per-value (by looking at which fields are nonzero in each value encountered).

The SQL/XML rule is to choose a static mapping at analysis time according to the type modifier. YEAR, MONTH, or YEAR TO MONTH call for a mapping to xs:yearMonthDuration, while any of the finer modifiers call for mapping to xs:dayTimeDuration, and no mapping is defined for an INTERVAL lacking a type modifier to constrain its fields in one of those ways. Again, those specified mappings assume that days are not subject to daylight rules, contrary to the behavior of the PostgreSQL type.

In view of those considerations, there seems to be no single mapping of PostgreSQL INTERVAL to a common Java type that is sufficiently free of caveats to stand as a reference implementation. An application ought to choose an implementation of this functional interface to create whatever representation of an INTERVAL will suit that application's purposes.

  • Field Details

  • Method Details

    • construct

      T construct(long microseconds, int days, int months)
      Constructs a representation T from the components of the PostgreSQL data type.

      PostgreSQL allows the three components to have independent signs. They are stored separately because the results of combining them with a date or a timestamp cannot be precomputed without knowing the other operand.

      In arithmetic involving an interval and a timestamp, the width of one unit in days can depend on the other operand if a timezone applies and has daylight savings rules: 

       SELECT (t + i) - t
 FROM (VALUES (interval '1' DAY)) AS s(i),
 (VALUES (timestamptz '12 mar 2022'), ('13 mar 2022'), ('6 nov 2022')) AS v(t);
 ----------------
  1 day
  23:00:00
  1 day 01:00:00

      In arithmetic involving an interval and a date or timestamp, the width of one unit in months can depend on the calendar month of the other operand, as well as on timezone shifts as for days: 

       SELECT (t + i) - t
 FROM (VALUES (interval '1' MONTH)) AS s(i),
 (VALUES (timestamptz '1 feb 2022'), ('1 mar 2022'), ('1 nov 2022')) AS v(t);
 ------------------
  28 days
  30 days 23:00:00
  30 days 01:00:00