Tracking the Current Transaction Start Date and Time

Jim_Knicely · March 2019

The built-in Vertica function TRANSACTION_TIMESTAMP returns a value of type TIME WITH TIMEZONE that represents the start of the current transaction.

It’s very useful for keeping track of when the transaction started for a group of table inserts.

Example:

dbadmin=> SELECT * FROM test;
c1 | created_timestamp | created_transaction_timestamp
----+-------------------+-------------------------------
(0 rows)

dbadmin=> INSERT INTO test SELECT 1, SYSDATE, TRANSACTION_TIMESTAMP();
OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO test SELECT 2, SYSDATE, TRANSACTION_TIMESTAMP();
OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO test SELECT 3, SYSDATE, TRANSACTION_TIMESTAMP();
OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT

dbadmin=> SELECT * FROM test;
c1 |     created_timestamp      | created_transaction_timestamp
----+----------------------------+-------------------------------
  1 | 2019-03-12 10:56:09.159499 | 2019-03-12 10:56:01.247889
  2 | 2019-03-12 10:56:21.087632 | 2019-03-12 10:56:01.247889
  3 | 2019-03-12 10:56:25.262153 | 2019-03-12 10:56:01.247889
(3 rows)

Helpful Link:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Date-Time/TRANSACTION_TIMESTAMP.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Date-Time/SYSDATE.htm

Have fun!