PostgreSQL: Finding the current timestamp

PostgreSQL provides all kinds of time-related functions. But time is not as easy as it seems. One question many people keep asking: How can I find the current timestamp? What time is it?

While this seems like an easy question the answer isn't as clear-cut as you might expect. Basically, there are three functions we might use to figure out what time it is:

• Real time
• Statement time
• Transaction time

Depending on what kind of time you want, different functions have to be called. When writing code, it is important to be aware of those subtle differences to ensure that your program is doing exactly what it's supposed to do:

now(): Determining transaction time in PostgreSQL

The most common way to determine time is to ask for “transaction time”. The idea is to have a function which “freezes time” and returns the same value throughout the transaction. The following listing shows an example:

As you can see, the timestamp is absolutely constant and does not change at all. Often “now” is seen as a default value. Sporadically this can cause bugs, because people expect time to change — which is not so in this case, by design.

statement_timestamp(): Your statement

In addition to now() there is also a way to find the timestamp of your current operation. In this case the timestamp will change inside your transaction but not inside your statement. You can rely on the fact that the same result will be returned throughout the entire (top level) operation.

The following listing shows how this works:

As you can see the pg_sleep() statement does not change the return value of the time function. It merely changes when the next statement starts.

clock_timestamp(): “Real time”

Finally, there is the clock_timestamp() function which returns the current timestamp. The important thing is that this function will return different values all the time. It does NOT guarantee to return different values but in reality it “almost” does. Here is an example showing how this works:

As you can see the timestamp changes all the time.

Finally …

When working with time it is important to understand which time of timestamp is needed. Do you need a constant return value or do you really need the current time? Depending on what is needed PostgreSQL provides the right function for you.

If you want to learn more about PostgreSQL check out Laurenz Albe's blog post about HOT Updates