It’s All About Timing

Every so often we hear about the seemingly confusing nature of SQL functions that return the “current” time. But what is ““current””? Is it the start time of a transaction or statement? Is it the time returned by the system clock? The answer is: all of these, depending on which function you call.

As you might expect, separating transaction start time, statement start time, and what colloquially call “current” time can become confusing quite quickly. But fear not. Vertica follows the conventions of other SQL databases, and after reading this blog, you’’ll have a clearer understanding about the differences between the date and time functions.

What function do I use?

So how do you determine which function to use to get the timestamp you want? We’’ll go over the functions in more detail in a moment. But when in doubt, consult the following table.

Although some functions have the same, well, function, we recommend using the functions in the first column because they are easiest to remember, as they actually return what they sound like they return.

Function	Description	Same as
TRANSACTION_TIMESTAMP()	Returns the start time of the current transaction.	NOW() CURRENT_TIMESTAMP() LOCALTIMESTAMP()
STATEMENT_TIMESTAMP()	Returns the start time of the current statement.	SYSDATE() GETDATE() GETUTCDATE()
CLOCK_TIMESTAMP	Returns time according to the internal system clock.	TIMEOFDAY()

When did my transaction start?

To figure out when a specific transaction started, we recommend using the TRANSACTION_TIMESTAMP function.
=> SELECT TRANSACTION_TIMESTAMP(), CLOCK_TIMESTAMP();
TRANSACTION_TIMESTAMP | CLOCK_TIMESTAMP
-------------------------------+-------------------------------
2016-03-09 08:54:59.042457-05 | 2016-03-09 08:54:59.065406-05
(1 row)
As you can see, the transaction timestamp (the time the transaction started) is different than the clock timestamp (the current time according to the system clock).

Now let’’s wait a minute or so and call the functions again:
=> SELECT TRANSACTION_TIMESTAMP(), CLOCK_TIMESTAMP();
TRANSACTION_TIMESTAMP | CLOCK_TIMESTAMP
-------------------------------+------------------------------
2016-03-09 08:54:59.042457-05 | 2016-03-09 08:56:32.73268-05
(1 row)
As you can see, the TRANSACTION_TIMESTAMP value does not change during the duration of this transaction. This behavior is to your benefit. The intent is to allow a single transaction to have a consistent notion of the “current time”, so that multiple modifications within the same transaction bear the same timestamp.

When did my statement start?

If you want to know when a specific statement started, we recommend using the STATEMENT_TIMESTAMP function. Like the TRANSACTION_TIMESTAMP function, this value does not change throughout the duration of the statement. The intent is the same as well: to allow a single statement to have a consistent notion of the current time, so that multiple modifications within the same statement bear the same timestamp.
=> SELECT STATEMENT_TIMESTAMP(), CLOCK_TIMESTAMP();
STATEMENT_TIMESTAMP | CLOCK_TIMESTAMP
------------------------------+-------------------------------
2016-03-09 09:02:45.518672-05 | 2016-03-09 09:02:45.526284-05
(1 row)
Like the TRANSACTION_TIMESTAMP, the STATEMENT_TIMESTAMP value differs slightly from the CLOCK_TIMESTAMP.

Let’’s call the functions again:
=> SELECT STATEMENT_TIMESTAMP(), CLOCK_TIMESTAMP();
STATEMENT_TIMESTAMP | CLOCK_TIMESTAMP
------------------------------+-------------------------------
2016-03-09 09:04:35.17659-05 | 2016-03-09 09:04:35.184708-05
(1 row)
We can see that the STATEMENT_TIMESTAMP has changed (because we ran a new statement), but the value still differs from the time read from the internal system clock.

What time is it now?

Remember back in the day when you used to have to call “the time lady” to figure out what time of day it was? You’d have your hand hovering over the clock on the microwave, waiting for “at the beep it will be exactly 3:12 pm”.

While that woman (or computer) is now out of a job, the need to know the exact time has not diminished. To find the time using Vertica, you use the CLOCK_TIMESTAMP function. This is the Vertica function we recommend you use to determine the system clock’’s time.

Because of this, this CLOCK_TIMESTAMP value can change within individual statements and transactions. All other Vertica functions listed in this blog reflect the start time of statements or transactions, which may or may not match the actual current time.

Timestamps in action

This graph gives an overview of what timestamp you might expect to see over a certain time period:

 

Time is of the essence

While we all wish the time functions would tell us it’’s five o’’clock on a Friday, retrieving the appropriate timestamp can be vital to the success of your organization. Through Vertica’’s numerous date and time retrieval abilities you can rest assure that time is on your side.

Learn More

For more information about date and time functions in Vertica, see our core documentation.