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:
2 | Column | Type | Modifiers |
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:
1 | select id, version, user_name, timestamp , |
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' |
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' |
Thx
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;