Postgres timestamptz not working with Hibernate reactive?

[burmanm]

Describe the bug I have a column in the Postgres with type of "timestamptz". However, trying to use these fields in the Hibernate always result in:

io.vertx.core.impl.NoStackTraceThrowable: Parameter at position[0] with class = [java.time.LocalDateTime] and value = [2020-07-16T10:10:24.378] can not be coerced to the expected class = [java.time.OffsetDateTime] for encoding.

It does not seem to matter what how I set the type. I have tried the following types: Date, LocalDateTime, OffsetDateTime and Date with @Temporal(TemporalType.TIMESTAMP). I have also tried with @CreationTimestamp, @PrePersist and the same with update methods also.

Always the same error, so the transformation to types happens outside my code and my classes seem to have no effect at all.

The code that does the persist:

    public Uni<Endpoint> createEndpoint(Endpoint endpoint) {
        return mutinySession.onItem().produceUni(session -> session.persist(endpoint))
                .onItem().produceUni(Mutiny.Session::flush)
                .onItem().apply(ignored -> endpoint)
                .onFailure().invoke(t -> {
                    System.out.printf("Failed to persist, %s\n", t.getMessage());
                });
    }

Expected behavior Timestamps should be persisted to Postgres.

Actual behavior Regardless of the type of property, the same error happens.

Environment (please complete the following information):

• Quarkus version or git rev: 1.6.0.Final

Additional context Dependencies related to db usage (if they matter):

    <dependency>
      <groupId>io.quarkus</groupId>
      <artifactId>quarkus-reactive-pg-client</artifactId>
    </dependency>
    <dependency>
      <groupId>io.quarkus</groupId>
      <artifactId>quarkus-hibernate-reactive</artifactId>
    </dependency>
    <dependency>
      <groupId>io.quarkus</groupId>
      <artifactId>quarkus-hibernate-reactive-deployment</artifactId>
    </dependency>

[quarkusbot]

/cc @aguibert, @gavinking, @Sanne /cc @gsmet, @Sanne

[Sanne]

Thanks @burmanm , we'll look into this. I suspect a mismatch in type support of the underling database driver, we might need to have Hibernate Reactive adjust to the supported types.

https://github.com/hibernate/hibernate-reactive/issues/285

[gavinking]

we might need to have Hibernate Reactive adjust to the supported types

As commented on that issue, I don't think this has anything to do with Hibernate Reactive itself.

[edufolly]

Hi folks, I have the same problem when I try to persist in a TIMESTAMP_WITH_TIMEZONE Postgres field.

I declare the attribute in the entity.

@Column(name = "created_at", nullable = false, updatable = false,
        columnDefinition = "timestamp with time zone not null")
public Date createdAt = new Date();

and I receive always the same error:

Caused by: io.vertx.core.impl.NoStackTraceThrowable: Parameter at position[7] with 
class = [java.time.LocalDateTime] and value = [2021-01-21T23:25:48.523] can not be 
coerced to the expected class = [java.time.OffsetDateTime] for encoding.

I also tried to create my own AbstractSingleColumnStandardBasicType but after the serialization, I have a similar error message.

Caused by: io.vertx.core.impl.NoStackTraceThrowable: Parameter at position[7] with 
class = [java.lang.String] and value = [2021-01-21 23:33:58.223-00] can not be 
coerced to the expected class = [java.time.OffsetDateTime] for encoding.

Researching how to solve the error I found this line that overrides the default type mappings in ReactiveSessionFactoryImpl.

ReactiveSessionFactoryImpl.java#L38

Removing this line maybe solve the Postgres issue? I don't know how to test... 😞

If you need further information, please ask me.

[Sanne]

@DavideD could you have a look at this one?

[DavideD]

ACK

Sure, I will check after lunch

On Wed, 26 May 2021, 12:02 Sanne Grinovero, @.***> wrote:

@DavideD https://github.com/DavideD could you have a look at this one?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/quarkusio/quarkus/issues/10768#issuecomment-848675891, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEIQ5LODEOP7UJZ3UHOZOTTPTIN3ANCNFSM4O3W322A .

[DavideD]

At the moment, one can solve this issue using a UserType: On the field:

       @Type(type = "org.example.TimestampWithTimezone")
    @Column(name = "created_at", nullable = false, updatable = false, columnDefinition = "timestamp with time zone not null")
    public Date createdAt = new Date();

The details about how to implement a UserType are on the Hibernate ORM documentation.

Here's a quick example of an implementation for this case:

    public class TimestampWithTimezone implements UserType {

        @Override
        public int[] sqlTypes() {
            return new int[] { Types.TIME_WITH_TIMEZONE };
        }

        @Override
        public Class returnedClass() {
            return Date.class;
        }

        @Override
        public boolean equals(Object x, Object y) throws HibernateException {
            return Objects.equals( x, y);
        }

        @Override
        public int hashCode(Object x) throws HibernateException {
            return Objects.hashCode(x);
        }

        @Override
        public Object nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner) throws HibernateException, SQLException {
            OffsetDateTime offsetDateTime = rs.getObject( names[0], OffsetDateTime.class );
            return offsetDateTime == null || rs.wasNull() ? null : new Date( offsetDateTime.toInstant().toEpochMilli() );
        }

        @Override
        public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session)
                throws HibernateException, SQLException {
            if ( value == null ) {
                st.setNull( index, Types.TIMESTAMP_WITH_TIMEZONE );
            }
            else {
                OffsetDateTime offsetDateTime = Instant
                        .ofEpochMilli( ( (Date) value ).getTime() )
                        .atOffset( ZoneOffset.UTC );

                st.setObject( index, offsetDateTime );
            }
        }

        @Override
        public Object deepCopy(Object value) throws HibernateException {
            return value;
        }

        @Override
        public boolean isMutable() {
            return false;
        }

        @Override
        public Serializable disassemble(Object value) throws HibernateException {
            return (Date) value;
        }

        @Override
        public Object assemble(Serializable cached, Object owner) throws HibernateException {
            return cached;
        }

        @Override
        public Object replace(Object original, Object target, Object owner) throws HibernateException {
            return original;
        }
    }

[amsterdam-superchat]

@DavideD Any updates? I guess Quarkus depend on a fix from hibernate reactive right?

[DavideD]

It's been some time since I've looked at this issue but I don't think we can solve it in Hibernate Reactive as discussed here.

The reason is that the type of the entity field is a Date and we expect to pass it to the vert.x client as a LocalDate. This would normally work because the column on the table would be a compatible type.

In the case of the issue the column type of the table has been changed to something else and now the Vert.x client expect an OffsetDateTime. The problem is that when the query get executed, Hibernate Reactive doesn't know the table column type and so it still passes a LocalDate even if now Vert.x expect an OffsetDateTime. I guess JDBC would do this kind of convertion automatically.

I think this issue is more a responsibility of the Vert.x SQL client team. timestamp with timezone in PostgreSQL only stores the date as UTC, so I'm not sure why passing a LocalDate is not allowed.

Note that the reason we pass a LocalDate in this case is that most databases don't have a column type that keeps track of the timezones, so it wouldn't be possible for Hibernate Reactive to read the correct timezone back.

At the moment, I can only see two options:

• Ask the team of the Vert.x SQL Client for PostgreSQL if they can change it so that's not so rigid (any other opinion about this?)
• Use a custom UserType

[DavideD]

I've created an issue for Vert.x to see what they think about it: https://github.com/eclipse-vertx/vertx-sql-client/issues/1122

[gesker]

Just a ping @DavideD

Ran into this migrating/refactoring a project from 2.8 (quarkus-hibernate-orm) to 2.10.1 (quarkus-hibernate-orm-panache reactive) java.time.OffsetDateTime is what we were using. Hope that both java.time.OffsetDateTime and ZonedDateTime make it into the fix, too.

As you pointed out above this does seem like it would be on the https://github.com/eclipse-vertx/vertx-sql-client/issues/1122 end of things. UserTypes for things foundational items like "time" aren't that attractive.

And, BTW, thank you for looking at this issue! VERY much appreciate your efforts. :)

[DavideD]

No problem :) Thanks for the feedback.

I would suggest to leave some comments on the Vert.x issue.

[DavideD]

The convertion from the raw value in the result set to OffsetDateTime happens in ResultSetAdaptor.

I will check ,but the issue should be about saving a temporal type without timezone as a Timestamptz. In that case you need to convert the type to an OffsetDateTime , otherwise Vert.x is not going to accept it as value for the insert. I think this can only happen is the db schema has not been generated by hibernate.

If the entity type is OffsetDateTime, it should work, though.

[DavideD]

Thanks @agreedSkiing for the projects.

I gave it a better look and the conversion from offset to LocalDateTime happens in the TimestampTypeDescriptor We register the type here: https://github.com/hibernate/hibernate-reactive/blob/c96add1c66b49c730273ae6bc0af08de30676d36/hibernate-reactive-core/src/main/java/org/hibernate/reactive/session/impl/ReactiveSessionFactoryImpl.java#L52

Hibernate Reactive will map an OffsetDateTime as timestamp. I'm starting to think that this was a bad idea, but the reasoning behind it is described in this issue: https://github.com/hibernate/hibernate-reactive/issues/285

That said, I'm not sure why it's a problem in Vert.x to store a LocalDateTime as a timestampz. You can read more about this here: https://github.com/eclipse-vertx/vertx-sql-client/issues/1122

I don't think there is much we can do at the moment about this, because:

• We are too close to Christmas and everyone is already in party mode for the holidays
• The Hibernate Reactive team is focusing on the integration with ORM 6

For now, I would suggest to use the workaround with the custom user type. I haven't tested it in a while, but it should allow you to map the value as you prefer. Maybe a converter will also work.

Beginning next year we should be able to have a better look at this issue and find a solution that's more user friendly.

[geoand]

Is this still an issue?

[brunobastosg]

Is this still an issue?

Yes, I just had this same problem and came across this issue. I had to create a custom user type, as suggested.