How to map the type Interval in Hibernate ?

Question

Hi, under PostGresql, Im using PersistentDuration for the mapping between the sql type interval & duration but it doesnt work. An other 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 doesnt work with the real format because it supposes 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/8.0/interactive/datatype-datetime.html

Have you a clean solution ? Thanks in advance Regards

Answer 1

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