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?

Stefan Falk · Accepted Answer · 2016-02-21 17:02:09Z

17

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

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

edited Feb 21, 2016 at 17:02

Stefan Falk

25.8k62 gold badges225 silver badges422 bronze badges

answered Jul 11, 2015 at 18:25

Alexei Osipov

77110 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cslotty Over a year ago

Is this actually useful only for the DDL auto or during runtime, too?

Alexei Osipov Over a year ago

columnDefinition value used only in DDL. However if your table column was created with that DDL it will indirectly affect specifics of runtime behavior too.

Leos Literak · Accepted Answer · 2023-01-08 17:46:41Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related