Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Tracking the Current Transaction Start Date and Time

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!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.