How to map the PostgreSQL Interval column type in Hibernate?

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; 
开发者_Go百科        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?

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;
    }

}

You don't have to write your own Hibernate custom type to map the PostgreSQL interval column to a Java Duration object. All you need to do is use the Hibernate Ttypes project.

So, after adding the proper Hibernate dependency:

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>2.6.0</version>
</dependency>

You just have to use the @TypeDef annotation to register the PostgreSQLIntervalType:

@Entity(name = "Book")
@Table(name = "book")
@TypeDef(
    typeClass = PostgreSQLIntervalType.class,
    defaultForType = Duration.class
)
@TypeDef(
    typeClass = YearMonthDateType.class,
    defaultForType = YearMonth.class
)
public class Book {
 
    @Id
    @GeneratedValue
    private Long id;
 
    @NaturalId
    private String isbn;
 
    private String title;
 
    @Column(
        name = "published_on",
        columnDefinition = "date"
    )
    private YearMonth publishedOn;
 
    @Column(
        name = "presale_period",
        columnDefinition = "interval"
    )
    private Duration presalePeriod;
 
    public Long getId() {
        return id;
    }
 
    public Book setId(Long id) {
        this.id = id;
        return this;
    }
 
    public String getIsbn() {
        return isbn;
    }
 
    public Book setIsbn(String isbn) {
        this.isbn = isbn;
        return this;
    }
 
    public String getTitle() {
        return title;
    }
 
    public Book setTitle(String title) {
        this.title = title;
        return this;
    }
 
    public YearMonth getPublishedOn() {
        return publishedOn;
    }
 
    public Book setPublishedOn(YearMonth publishedOn) {
        this.publishedOn = publishedOn;
        return this;
    }
 
    public Duration getPresalePeriod() {
        return presalePeriod;
    }
 
    public Book setPresalePeriod(Duration presalePeriod) {
        this.presalePeriod = presalePeriod;
        return this;
    }
}

Now, when persisting the Book entity:

entityManager.persist(
    new Book()
        .setIsbn("978-9730228236")
        .setTitle("High-Performance Java Persistence")
        .setPublishedOn(YearMonth.of(2016, 10))
        .setPresalePeriod(
            Duration.between(
                LocalDate
                    .of(2015, Month.NOVEMBER, 2)
                    .atStartOfDay(),
                LocalDate
                    .of(2016, Month.AUGUST, 25)
                    .atStartOfDay()
            )
        )
);

Hibernate will execute the proper SQL INSERT statement:

INSERT INTO book (
    isbn,
    presale_period,
    published_on,
    title,
    id
)
VALUES (
    '978-9730228236',
    '0 years 0 mons 297 days 0 hours 0 mins 0.00 secs',
    '2016-10-01',
    'High-Performance Java Persistence',
    1
)

When fetching the Book entity, we can see that the Duration attribute is properly fetched from the database:

Book book = entityManager
    .unwrap(Session.class)
    .bySimpleNaturalId(Book.class)
    .load("978-9730228236");
 
assertEquals(
    Duration.between(
        LocalDate
            .of(2015, Month.NOVEMBER, 2)
            .atStartOfDay(),
        LocalDate
            .of(2016, Month.AUGUST, 25)
            .atStartOfDay()
    ),
    book.getPresalePeriod()
);

Why not just turn it into a numeric and map to a Long?

SELECT EXTRACT(epoch FROM my_interval)

PostgreSQL has a date_part / extract function which you can use to return different fields, epoch being one of them. When extracting the epoch from an interval, you receive the number of seconds contained in the interval, and from there you can convert however you wish. I lost my experience with Hibernate, but you can do it this way:

SELECT
    average_interval_between_airings
  , date_part('epoch', average_interval_between_airings) / 60 as minutes
  , date_part('epoch', average_interval_between_airings) as seconds
FROM shows;

According to the link you should have a day followed by hours:minutes:seconds. So changing the code to something like the following assuming you never need to have more then 23 hours 59 minutes in the interval.

statement.setString(index, "'0 "+ hours +":" 
+ intervalFormat.format(minutes) + ":" 
+ intervalFormat.format(secondes)+"'"); 

I am not able to test this code since I don't have PostGreSql installed. For another discussion on this same issue see the following link, although you would have to modify the code provided to handle seconds. That shouldn't be much of a problem though.

https://forum.hibernate.org/viewtopic.php?p=2348558&sid=95488ce561e7efec8a2950f76ae4741c