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:

             Table "public.revinfo"
  Column   |          Type          | Modifiers 
-----------+------------------------+-----------
 id        | integer                | not null
 version   | bigint                 | not null
 timestamp | bigint                 | not null
 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:

select id, version, user_name, timestamp,
  to_char(
    TO_DATE('01/01/1970 00:00:00','DD/MM/YYYY HH24:MI:SS')
    + (r.timestamp /1000/60/60/24 ||' day')::interval, 'YYYY/MM/DD'
  ) as rev_date, 
  to_char(
    TO_DATE('01/01/1970 00:00:00','DD/MM/YYYY HH24:MI:SS') 
    + (r.timestamp /1000/60/60/24 ||' day')::interval, 'YYYY/MM'
  ) as rev_month 
from revinfo r;

136

59