How to operate on PostgreSQL interval datatype using jdbc/spring-jdbc not using PGInterval?

Question

Java date & time datatypes are as everybody knows, there's no need to focus on that.

However, when I'm using JDBC or Spring-based extensions, such as SimpleJdbcTemplate to retrieve and store interval values, what Java type should I use, if I don't want to use org.postgresql.util.PGInterval class?

This class is internal of PostgreSQL driver so using it would make the code DB-specific. I think it should be possible to operate on intervals in DB-agnostic way, because it is one of standard SQL types.

Answer 1

An interval isn't one of the standard JDBC Types, as listed in the java.sql.Types class. I know that if you call resultSet.getObject("interval_column"), it is a PGInterval when casted, so it looks like the PG JDBC driver might be forcing your hand to deal with it as such, unless you do what Glenn says and convert it to a string, or maybe a number, in your SQL.

In our application, we use JodaTime for all of our date management, and we have a Hibernate Type written that converts our bean property to and from a PGInterval, and use getObject and setObject to communicate with JDBC. I doubt that code would help you deal with what you are looking for here, but I can share it with you if you are interested.

UPDATED: Here is the Hibernate Type class that converts between Joda Time and PGInterval. I know this doesn't answer the question, but the original poster asked for the sample code.

package com.your.package.hibernate.types;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;
import org.joda.time.DurationFieldType;
import org.joda.time.Period;
import org.joda.time.ReadableDuration;
import org.joda.time.ReadablePeriod;
import org.postgresql.util.PGInterval;

public class JodaTimeDurationType
    implements UserType {

    public Class<?> returnedClass() {
        return ReadableDuration.class;
    }


    public int[] sqlTypes() {
        return new int[] {Types.OTHER};
    }


    public Object nullSafeGet(ResultSet resultSet, String[] names, Object owner)
        throws HibernateException, SQLException {

        try {
            final PGInterval pgi = (PGInterval)resultSet.getObject(names[0]);

            final int years = pgi.getYears();
            final int months = pgi.getMonths();
            final int days = pgi.getDays();
            final int hours = pgi.getHours();
            final int mins = pgi.getMinutes();
            final double secs = pgi.getSeconds();

            return new Period(years, months, 0, days, hours, mins, (int)secs, 0).toStandardDuration();

        }
        catch (Exception e) {
            return null;
        }
    }


    public void nullSafeSet(PreparedStatement statement, Object value, int index)
        throws HibernateException, SQLException {

        if (value == null) {
            statement.setNull(index, Types.OTHER);
        }
        else {
            final ReadablePeriod period = ((ReadableDuration)value).toPeriod();

            final int years = period.get(DurationFieldType.years());
            final int months = period.get(DurationFieldType.months());
            final int days = period.get(DurationFieldType.days());
            final int hours = period.get(DurationFieldType.hours());
            final int mins = period.get(DurationFieldType.minutes());
            final int secs = period.get(DurationFieldType.seconds());

            final PGInterval pgi = new PGInterval(years, months, days, hours, mins, secs);
            statement.setObject(index, pgi);
        }
    }


    public boolean equals(Object x, Object y)
        throws HibernateException {

        return x == y;
    }


    public int hashCode(Object x)
        throws HibernateException {
        return x.hashCode();
    }


    public Object deepCopy(Object value)
        throws HibernateException {
        return value;
    }


    public boolean isMutable() {
        return false;
    }


    public Serializable disassemble(Object value)
        throws HibernateException {
        throw new HibernateException("not implemented");
    }


    public Object assemble(Serializable cached, Object owner)
        throws HibernateException {
        throw new HibernateException("not implemented");
    }


    public Object replace(Object original, Object target, Object owner)
        throws HibernateException {
        throw new HibernateException("not implemented");
    }
}