Failed to store java.time.ZonedDateTime using hibernate PostgreSQL

Question

I have an entity which extends an auditing entity class named AbstractAuditingEntity in which one of the field is

@CreatedDate
@Column(name = "created_date", nullable = false)
@JsonIgnore
private ZonedDateTime createdDate

Above field has mapping to database field named "created_date" with type "timestamp without time zone".

But at the time of saving this entity PostgresSQL throws error as follows :

Caused by: org.postgresql.util.PSQLException: ERROR: column "created_date" is of type timestamp without time zone but expression is of type bytea
  Hint: You will need to rewrite or cast the expression.

I looked for the same error & found solution here : Postgresql UUID supported by Hibernate?

But the solution is for java.util.UUID, in solution it is suggested to add annotation @Type(type="pg-uuid") on a field with UUID type.

Is there any such a ready-to-use type value like pg-uuid for ZonedDateTime ? Any reference link for the different values constants of hibernate @Type annotation ?

Or am supposed to write a custom deserializer class ? How to write such deserializer class any reference link ?

PostgresSQL Version  : 9.6,
<liquibase-hibernate5.version>3.6</liquibase-hibernate5.version>

Can you enable debug logs on org.hibernate.type in your logger? You should find something like this if the types are registered pastebin.com/t2xh5UvM and should work. — coladict
– coladict, Commented Jan 18, 2017 at 11:35
Before Hibernate 5.2 the handling of Java 8 time classes came in an optional module. — coladict
– coladict, Commented Jan 18, 2017 at 11:40

teppic · Accepted Answer · 2017-01-18 19:29:25Z

8

For Hibernate 5.2 it should just work out of the box.

For 5.0 you can add a dependency to support it:

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-java8</artifactId>
    <version>${hibernate.version}</version>
</dependency>

For 4.3 you can use a JPA 2.1 converter:

@Converter
public class TimestampConverter implements AttributeConverter<ZonedDateTime, Timestamp> {
    @Override
    public Timestamp convertToDatabaseColumn(ZonedDateTime zonedTime) {
        if (zonedTime == null) {
            return null;
        }
        return new Timestamp(zonedTime.toInstant().toEpochMilli());
    }

    @Override
    public ZonedDateTime convertToEntityAttribute(Timestamp localTime) {
        if (localTime == null) {
            return null;
        }
        return ZonedDateTime.ofInstant(Instant.ofEpochMilli(localTime.getTime()), ZoneId.systemDefault());
    }
}

Then annotate your attribute with @Convert:

@Convert(converter = TimestampConverter.class)
private ZonedDateTime createdDate;

I note that your database column is TIMESTAMP. It should really be TIMESTAMP WITH TIME ZONE, especially if your client time zone has daylight savings changes.

edited Jan 18, 2017 at 19:29

answered Jan 18, 2017 at 8:25

teppic

7,3161 gold badge34 silver badges38 bronze badges

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

2 Comments

jannis Over a year ago

Notice that you lose timezone information in your solution for 4.3.

teppic Over a year ago

Postgres doesn't store offset information in TIMESTAMP or TIMESTAMP WITH TIME ZONE.

Collectives™ on Stack Overflow

Failed to store java.time.ZonedDateTime using hibernate PostgreSQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related