How to map the PostgreSQL Interval column type in Hibernate?

Question

Under PostgreSQL, I'm using PersistentDuration for the mapping between the sql type interval & duration but it doesn't work.

Another user found the same issue & come with his own class:

public void nullSafeSet(PreparedStatement statement, Object value, int index) 
        throws HibernateException, SQLException { 
    if (value == null) { 
        statement.setNull(index, Types.OTHER); 
    } else { 
        Long interval = ((Long) value).longValue(); 
        Long hours = interval / 3600; 
        Long minutes = (interval - (hours * 3600)) / 60; 
        Long secondes = interval - (hours * 3600) - minutes * 60; 
            statement.setString(index, "'"+ hours +":" 
                    + intervalFormat.format(minutes) + ":" 
                    + intervalFormat.format(secondes)+"'"); 

    } 
}

But it doesn't work with the real format because it suppose the interval pattern is only "hh:mm:ss". That is not the case: see

Here some few real examples i need to parse from the database:

1 day 00:29:42
00:29:42
1 week 00:29:42
1 week 2 days  00:29:42
1 month 1 week 2 days  00:29:42
1 year 00:29:42
1 decade 00:29:42

http://www.postgresql.org/docs/current/interactive/datatype-datetime.html

Have you a clean solution?

Answer 1

This is a working solution for JPA, Hibernate (with annotations).

This is the beginning of the entity class (for the table that has Interval column):

@Entity
@Table(name="table_with_interval_col")
@TypeDef(name="interval", typeClass = Interval.class)
public class TableWithIntervalCol implements Serializable {

This is the interval column:

@Column(name = "interval_col",  nullable = false)
@Type(type = "interval")    
private Integer intervalCol;

And this is the Interval class:

package foo.bar.hibernate.type;

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

import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;
import org.postgresql.util.PGInterval;


/**
 * Postgres Interval type
 * 
 * @author bpgergo
 * 
 */
public class Interval implements UserType {
    private static final int[] SQL_TYPES = { Types.OTHER };

    @Override
    public int[] sqlTypes() {
        return SQL_TYPES;
    }

    @SuppressWarnings("rawtypes")
    @Override
    public Class returnedClass() {
        return Integer.class;
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {
        return x.equals(y);
    }

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

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
            throws HibernateException, SQLException {
        String interval = rs.getString(names[0]);
        if (rs.wasNull() || interval == null) {
            return null;
        }
        PGInterval pgInterval = new PGInterval(interval);
        Date epoch = new Date(0l);
        pgInterval.add(epoch);
        return Integer.valueOf((int)epoch.getTime() / 1000);
    }

    public static String getInterval(int value){
        return new PGInterval(0, 0, 0, 0, 0, value).getValue();
    }


    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index)
            throws HibernateException, SQLException {
        if (value == null) {
            st.setNull(index, Types.VARCHAR);
        } else {
            //this http://postgresql.1045698.n5.nabble.com/Inserting-Information-in-PostgreSQL-interval-td2175203.html#a2175205
            st.setObject(index, getInterval(((Integer) value).intValue()), Types.OTHER);
        }
    }

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

    @Override
    public boolean isMutable() {
        return false;
    }

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        return (Serializable) value;
    }

    @Override
    public Object assemble(Serializable cached, Object owner)
            throws HibernateException {
        return cached;
    }

    @Override
    public Object replace(Object original, Object target, Object owner)
            throws HibernateException {
        return original;
    }

}