How to map postgresql "timestamp with time zone" in a JPA 2 entity

hibernate, jpa, jpa-2.0, postgresql

Solution

I eventually made this "work" - in a hackish sort of way - by turning off schema validation.

Previously, I had `<property name="hibernate.hbm2ddl.auto" value="validate"/>"hibernate.hbm2ddl.auto"` in my persistence.xml. When I commented out this property, my app server started and the model "worked".

The final form of my entity was:

@Entity
@Table(schema = "content", name = "theme")
public class Theme extends AbstractBaseEntity {
    private static final long serialVersionUID = 1L;

    @Column(name = "run_from", columnDefinition = "timestamp with time zone not null")
    @NotNull
    @Temporal(TemporalType.TIMESTAMP)
    private Date runFrom;

    @Column(name = "run_to", columnDefinition = "timestamp with time zone not null")
    @NotNull
    @Temporal(TemporalType.TIMESTAMP)
    private Date runTo;
    
    /* Getters, setters, .hashCode(), .equals() etc omitted */

After reading quite a bit on this, I got the impression is that there is no easy way to map Postgresql timestamp with time zone columns.

Some JPA implementation + database combinations support this natively ( EclipseLink + Oracle is one example ). For hibernate, with jodatime extensions, it is possible to store timezone aware timestamps using a normal timestamp + a varchar field for the timezone( I could not do that since I was constrained from changing the database schema ). Jadira user types or completely custom user types can also be used to tackle this problem.

I need to note that my use-case for this entity is "read only", so I could get away with a seemingly naive "solution".

Problem

I have a JPA 2 application ( with Hibernate 3.6 as the JPA implementation ) that uses Postgresql ( with the 9.0-801.jdbc3 JDBC driver ). I am having trouble mapping "timestamp with time zone" fields into my JPA entities. Here is an example: ``` CREATE TABLE theme ( id serial NOT NULL, # Fields that are not material to the question have been edited out run_from timestamp with time zone NOT NULL, run_to timestamp with time zone NOT NULL, CONSTRAINT theme_pkey PRIMARY KEY (id ), CONSTRAINT theme_name_key UNIQUE (name ) ) ``` I have tried to map as follows: ``` @Entity @Table(schema = "content", name = "theme") public class Theme extends AbstractBaseEntity { private static final long serialVersionUID = 1L; @Column(name = "run_from") @NotNull @Temporal(TemporalType.TIMESTAMP) private Date runFrom; @Column(name = "run_to") @NotNull @Temporal(TemporalType.TIMESTAMP) private Date runTo; /* The rest of the entity has been edited out */ ``` I keep on getting an exception with the following root cause: `Caused by: org.hibernate.HibernateException: Wrong column type in public.backend_themetopic for column created. Found: timestamptz, expected: date` What I have tried - replacing `java.util.Calendar` with `java.util.Date` - made no difference - using `java.sql.Timestamp` - complained that I cannot apply the `@Temporal` annotation to a `Timestamp` - using `org.joda.time.DateTime` with a custom `@Type` annotation ( `@Type(type="org.joda.time.contrib.hibernate.PersistentDateTimeTZ")` ) also did not work Constraints - This application interacts with a "legacy system" - so, changing the types of the date fields is not a good option My question is: how should I map these timezone aware timestamps into my JPA entities?

Original source