Date and Time Functions in Transactions

[JimKnicely]

There are a myriad of built in functions in Vertica that can be queried to determine the date and time. They can be separated into two groups based on how their results differ depending on when they are called from within a database Transaction.

The first group of functions report the date and time at the beginning of a Transaction. Their return values do not change as the Transaction progresses and remain constant until the Transaction ends (via a COMMIT or ROLLBACK):

Code: Select all

dbadmin=> COMMIT; -- End the current Transaction
COMMIT 
dbadmin=> SELECT current_date "current_date",
dbadmin->        current_time "current_time",
dbadmin->        current_timestamp "current_timestamp",
dbadmin->        localtime "localtime",
dbadmin->        localtimestamp "localtimestamp",
dbadmin->        transaction_timestamp(),
dbadmin->        now();
-[ RECORD 1 ]---------+------------------------------
current_date          | 2012-08-28
current_time          | 14:24:02.018544-04
current_timestamp     | 2012-08-28 14:24:02.018544-04
localtime             | 14:24:02.018544
localtimestamp        | 2012-08-28 14:24:02.018544
transaction_timestamp | 2012-08-28 14:24:02.018544-04
now                   | 2012-08-28 14:24:02.018544-04

dbadmin=>
dbadmin=> SELECT SLEEP(10); -- Sleep for 10 seconds; Transaction is in progress
-[ RECORD 1 ]
SLEEP | 0

dbadmin=>
dbadmin=> -- Notice that the date and times that follow have not changed since the Transaction started!
dbadmin=> SELECT current_date "current_date",
dbadmin->        current_time "current_time",
dbadmin->        current_timestamp "current_timestamp",
dbadmin->        localtime "localtime",
dbadmin->        localtimestamp "localtimestamp",
dbadmin->        transaction_timestamp(),
dbadmin->        now();
-[ RECORD 1 ]---------+------------------------------
current_date          | 2012-08-28
current_time          | 14:24:02.018544-04
current_timestamp     | 2012-08-28 14:24:02.018544-04
localtime             | 14:24:02.018544
localtimestamp        | 2012-08-28 14:24:02.018544
transaction_timestamp | 2012-08-28 14:24:02.018544-04
now                   | 2012-08-28 14:24:02.018544-04

The second group of functions report the date and time per statement within the Transaction. Their result values do change as the Transaction progresses.

Code: Select all

dbadmin=> COMMIT; -- End the current Transaction
COMMIT

dbadmin=> SELECT sysdate,
dbadmin->        getdate(),
dbadmin->        clock_timestamp(),
dbadmin->        statement_timestamp();
-[ RECORD 1 ]-------+------------------------------
sysdate             | 2012-08-28 14:29:05.718853
getdate             | 2012-08-28 14:29:05.718853
clock_timestamp     | 2012-08-28 14:29:05.727255-04
statement_timestamp | 2012-08-28 14:29:05.718853-04

dbadmin=> SELECT SLEEP(10); --Sleep for 10 seconds; Transaction is in progress
-[ RECORD 1 ]
SLEEP | 0

dbadmin=> -- Notice that the date and times that follow have changed since Transaction started!
dbadmin=> SELECT sysdate,
dbadmin->        getdate(),
dbadmin->        clock_timestamp(),
dbadmin->        statement_timestamp();
-[ RECORD 1 ]-------+------------------------------
sysdate             | 2012-08-28 14:29:16.04806
getdate             | 2012-08-28 14:29:16.04806
clock_timestamp     | 2012-08-28 14:29:16.065945-04
statement_timestamp | 2012-08-28 14:29:16.04806-04

Have fun!