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

Question

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?

Answer 1

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 = "timestampt 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".

Answer 2

Add @Column(columnDefinition= "TIMESTAMP WITH TIME ZONE")

@Column(name = "run_from", columnDefinition= "TIMESTAMP WITH TIME ZONE") @NotNull @Temporal(TemporalType.TIMESTAMP) private Date runFrom;