Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Failed to store java.time.ZonedDateTime using hibernate PostgreSQL

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>
like image 268
Prashant Avatar asked Jan 18 '17 06:01

Prashant


1 Answers

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.

like image 196
teppic Avatar answered Sep 18 '22 10:09

teppic