PostgreSQL: Getting now() in functions

I come from a world where databases are less SQL standards compliant. So as I was getting into PostgreSQL, I remember spending a lot of time learning about time. One of these time-consuming time investigations was troubleshooting my functions to figure out why I couldn’t seem to get more than one current timestamp value. I recently made some updates to a few procedures with logging, and could not remember the preferred way to accomplish current timestamps, so I went digging again. Since it always takes me more than a few moments to find this information, I thought I would post it here. This way I’ll remember it, and hopefully it will save some people the agony of searching the docs for a simple function or two.

clock_timestamp() and timeofday()

The short answer is: you probably want to remove “now()” or “CURRENT_TIMESTAMP”, and replace it with “clock_timestamp()” or “timeofday()”.

The reference for this information is available in the docs (of course, rtfm) here: PostgreSQL 9.0.2 Documentation: Date/Time Functions and Operators.

Before I highlight some bits from the documenation, here is an example of our “problem”.

MySQL current_timestamp behavior

In MySQL - generate a function that depends on time progressing as it runs, notice that the current timestamps for ABC and DEF differ by approximately 10 seconds after running this function.

PostgreSQL current_timestamp behavior

In PostgreSQL - generating an equivalent function for our tests results in something like the following. Notice that CURRENT_TIMESTAMP remains consistent with the timestamp at the beginning of the transaction (In MySQL you would persist this value by storing it manually):

In many cases it won’t matter, because these efforts will generally be focused on logging and documentation, but it’s worth noting that “timeofday()” will return you the same time as “clock_timestamp()”. However, “timeofday()” returns the value as text, while “clock_timestamp()” returns a timestamp.

So, the basic rundown is:

• transaction_timestamp() == CURRENT_TIMESTAMP == now()
• clock_timestamp()::TEXT == timeofday()
• clock_timestamp() == timeofday()::TIMESTAMP