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
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;