Re: PostgreSQL interval type handling?

[Arturo P�rez <ap...@hayesinc.com>]

In article <84...@objectstyle.org>,
 Andrus Adamchik <an...@objectstyle.org> wrote:

> No dragons, just ExtendedType:
> 
> http://cwiki.apache.org/CAYDOC/extended-types.html
> 
> Andrus
> 
> 
> >>
> >> On Sep 20, 2006, at 11:20 PM, Arturo P�rez wrote:
> >>> Hi all,
> >>>
> >>> I would like to use the PostgreSQL interval type in Cayenne.
> >>> Do I need to create a custom type?  If so, are there any gotchas?
> >>>
> >>> tia,
> >>> arturo
> >>>
> >>>
> >
> >
> >
> > OK.  Turns out that the JDBC driver for PostgreSQL has a PGInterval
> > type.  I set the class attribute to that type
> > (org.postgresql.util.PGinterval) and the Java code was generated
> > correctly.  I haven't run it yet, though.  Is there anything else I
> > should do?
> >
> > There any dragons lurking in there that I should be aware of?
> >
> > tia,
> > arturo
> >
> >



Just to partially close the loop on this one.  For a read-only 
application the below recipe works fine.I ran into one teensy problem 
easily resolved.  The modeler only knows timestamps, not
intervals, and that fooled me for a bit.

To use/read PostgreSQL interval types with JDBC:

1.  You have to create the column by hand because the CayenneModeler
does not know the interval type.

2.  In the DbObject, set the column type to OTHER.

3.  In the ObjType set the attribute's class to 
org.postgresql.util.PGinterval.

Voila!  Cayenne can read interval types and return them no problem.  
Please note that intervals cannot be cast to timestamps (my teensy 
problem) but as the PostgreSQL JDBC driver provides a PGInterval type,
that's not a problem.

-arturo