Reading the timestamp data from the hibernate envers revinfo table

A short blog post in case this helps anyone!

We’re using Hibernate Envers to keep an audit trail of data changes. I thought I’d have a look into the (Postgres) database to get some stats on usage – how many records of a certain type we’re creating by month for example.

The “data_aud” tables have a “rev” field which joins to the “revinfo” table, which looks like this:

1             Table "public.revinfo"
2  Column   |          Type          | Modifiers
3-----------+------------------------+-----------
4 id        | integer                | not null
5 version   | bigint                 | not null
6 timestamp | bigint                 | not null
7 user_name | character varying(255) | not null

I was surprised to see that the timestamp was of type bigint – the DDL was generated by Envers, not us!

Attempting to get realistic dates out of the field using Postgres functions such as

to_timestamp(revinfo.timestamp)

failed dismally.

I found this post which gives a working answer for Oracle: SQL query to get a human readable display of the *number* REVINFO.timestamp ?

With a bit of tweaking, here is a useful Postgres view which I created to make future querying a bit easier:

1select id, version, user_name, timestamp,
2  to_char(
3    TO_DATE('01/01/1970 00:00:00','DD/MM/YYYY HH24:MI:SS')
4    + (r.timestamp /1000/60/60/24 ||' day')::interval, 'YYYY/MM/DD'
5  ) as rev_date,
6  to_char(
7    TO_DATE('01/01/1970 00:00:00','DD/MM/YYYY HH24:MI:SS')
8    + (r.timestamp /1000/60/60/24 ||' day')::interval, 'YYYY/MM'
9  ) as rev_month
10from revinfo r;

2 thoughts on “Reading the timestamp data from the hibernate envers revinfo table

  1. Had a similar requirement and came to this for postgres, keeping the milliseconds:

    create view timewindow as
    select id, username,
    to_timestamp(’01-01-1970 00:00:00′, ‘DD-MM-YYYY HH24:MI:SS’) + timestamp * interval ‘1 millisecond’
    as timestamp
    from revisioninfo;

Leave a Reply to Emir Cortes Cancel reply

Your email address will not be published. Required fields are marked *