How Hibernate's enver @AuditTable and @CreationTimestamp and different date format for main and public schema cause wrong values in the main schema?

Question

We have an audit schema and Hibernate enver to audit the data:

spring:
 jpa:
  org:
    hibernate:
      envers:
        default_schema: audit

and here are the anoontations.

BaseEntity:

@Data
@MappedSuperclass
@EqualsAndHashCode(of = {"id"})
public abstract class BaseEntity implements Serializable {

    @Id
    @GeneratedValue(generator = "UUID")
    @GenericGenerator(name = "UUID", strategy = "org.hibernate.id.UUIDGenerator"
            ,parameters = {
            @Parameter( name = "uuid_gen_strategy_class", value = "org.hibernate.id.uuid.CustomVersionOneStrategy")
    })
    @Column(name="id", updatable = false, nullable = false, columnDefinition = "uuid")
    private UUID id;


    @CreationTimestamp
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "created_at", updatable = false, nullable = false)
    private Date createdAt;    
}

other entities extend it, as well as the audit set up on it too.

Say, Contractor entity:

@Data
@NoArgsConstructor
@Entity
@Audited
@AuditTable(schema = "audit", value = "contractor")
@AuditOverride(forClass = BaseEntity.class, isAudited = true)
@Table(name = "contractor", uniqueConstraints = {@UniqueConstraint(name = "contractor_upi_uq", columnNames = {"upi"})})
@org.hibernate.annotations.Table(comment = "Contractor data", appliesTo = "contractor")
@EqualsAndHashCode(of = {}, callSuper = true)
@ToString(exclude = {"contract", "billingAccount", "legalForm", "services", "contractorType", "serviceGroups"})
@JsonIgnoreProperties(value = {"contract", "billingAccount", "legalForm", "services", "contractorType", "serviceGroups"})
public class Contractor extends BaseEntity {    
//specific fields are ommited as the problem are related to the field from the Base entity    
}

or ServiceOrder entity

@Data
@NoArgsConstructor
@Entity
@Audited
@AuditTable(schema = "audit", value = "services_order")
@AuditOverride(forClass = BaseEntity.class, isAudited = true)
@Table(name = "services_order")
@org.hibernate.annotations.Table(comment = "Services order", appliesTo = "services_order")
@EqualsAndHashCode(of = {}, callSuper = true)
@ToString
@JsonIgnoreProperties
public class ServicesOrder  extends BaseEntity {
//the same for the internals of this entity
}

We notices, that the entities of the same time of creation have different created_at values, for example:

The created_at column has type timestamp without timezone, for some entries it was in the local timezone, while in others +3h, in others -3h (our timezone is +3 UTC).

That happened on UAT db, but never happened on dev db, where all entries are from the same time.

I tired to find what the problem correlates with and have fount that despite the created_at has the same timestamp without timezone for the different tables of the main public schema, the same and corresponding created_at field has different type in audit schema for some tables. If the audit's field has type timestamp without timezone, the date in the field of the main schema is as expected and fits the local timezone time. If the audit schema's field created_at has type timestamp with timezone, the value in the main public schema's is -3 h (not as expected).

What could be the real reason for that? Does @CreationTimestamp really interacts with the enver and audit table before produce the output date to the main schema table? Is there a way to work this around not even touching the audit schema>

How Hibernate's enver @AuditTable and @CreationTimestamp and different date format for main and public schema cause wrong values in the main schema?

0 Answers0