Tracking the Current Transaction Start Date and Time

Posted March 14, 2019 by Jim Knicely, Vertica Principal Solution Architect

The built-in Vertica function TRANSACTION_TIMESTAMP returns a value of type TIME WITH TIMEZONE, which 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 Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Date-Time/TRANSACTION_TIMESTAMP.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Date-Time/SYSDATE.htm