PostgreSQL Commit timestamp tracking

A boolean configuration parameter named track_commit_timestamp is now available. "on" enables commit timestamp tracking. This makes it possible to see when a transaction was committed. (timestamp with timezone of the servertime)

To query that information, use the function pg_xact_commit_timestamp(transaction_id) to find out when rows were INSERTed/UPDATEed, call the pg_xact_commit_timestamp function passing in the xmin system column. Note that you have to set the parameter and restart before it can start tracking timestamps, so it won't show up until you do so:

 # SELECT pg_xact_commit_timestamp(xmin), * FROM colours;
  pg_xact_commit_timestamp | id | name  
 --------------------------+----+-------
                           |  1 | red
                           |  2 | green
                           |  3 | blue
 (3 rows)

Then we edit postgresql.conf so that track_commit_timestamp = on, then restart PostgreSQL:

 # INSERT INTO colours VALUES ('mauve'),('cyan'),('indigo');
 INSERT
 
 # SELECT pg_xact_commit_timestamp(xmin), * FROM colours;
    pg_xact_commit_timestamp    | id |  name  
 -------------------------------+----+--------
                                |  1 | red
                                |  2 | green
                                |  3 | blue
  2015-10-02 11:16:34.678267+01 |  4 | mauve
  2015-10-02 11:16:34.678267+01 |  5 | cyan
  2015-10-02 11:16:34.678267+01 |  6 | indigo
 (6 rows)

You can see that the first 3 rows that were inserted before the parameter was enabled don't return the timestamp, so it only starts to keep track of timestamps for INSERTs/UPDATEs once the parameter has been enabled and the database cluster has been restarted.

There is also the set-returning function pg_last_committed_xact which tells you the timestamp of the last committed transaction:

 # SELECT * FROM pg_last_committed_xact();
  xid  |           timestamp           
 ------+-------------------------------
  2039 | 2015-10-02 11:16:34.678267+01
 (1 row)