Tracking the Current Transaction Start Date and Time
Jim_Knicely
Administrator
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!
0