Postgres now() timestamp doesn't change, when script works

Question

My PHP script will work for several seconds, and I need to get real timestamp from Postgres database anytime. But when I fetch current_timestamp from Postgres, it always return same value.

Here is my example script (using DBAL):

 echo "DB:" . $dbal->fetchColumn("select current_timestamp") . PHP_EOL;
 echo "PHP: " . date("Y-m-d H:i:s") . PHP_EOL;
 sleep(3);
 echo "DB:" . $dbal->fetchColumn("select current_timestamp") . PHP_EOL;
 echo "PHP: " . date("Y-m-d H:i:s") . PHP_EOL;
 sleep(3);
 echo "DB:" . $dbal->fetchColumn("select current_timestamp") . PHP_EOL;
 echo "PHP: " . date("Y-m-d H:i:s") . PHP_EOL;

Here is the output:

DB:2014年05月07日 11:59:50.118+04
PHP: 2014年05月07日 11:59:50
DB:2014年05月07日 11:59:50.118+04
PHP: 2014年05月07日 11:59:53
DB:2014年05月07日 11:59:50.118+04
PHP: 2014年05月07日 11:59:56

Postgres installed on local machine. Why it always returns same time? How to get real time?

I used now() function, there was same result. Postgres version: 9.3.4 x64. PHP version: 5.5.11

Answer 1

From TFM, highlights mine:

9.9.4. Current Date/Time

PostgreSQL provides a number of functions that return values related to the current date and time. These SQL-standard functions all return values based on the start time of the current transaction:

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME(precision)
CURRENT_TIMESTAMP(precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME(precision)
LOCALTIMESTAMP(precision)

...

Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: 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 time stamp.

PostgreSQL also provides functions that return the start time of the current statement, as well as the actual current time at the instant the function is called. The complete list of non-SQL-standard time functions is:

transaction_timestamp()
statement_timestamp()
clock_timestamp()
timeofday()
now()

transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, but is named to clearly reflect what it returns. statement_timestamp() returns the start time of the current statement (more specifically, the time of receipt of the latest command message from the client). statement_timestamp() and transaction_timestamp() return the same value during the first command of a transaction, but might differ during subsequent commands. clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command. timeofday() is a historical PostgreSQL function. Like clock_timestamp(), it returns the actual current time, but as a formatted text string rather than a timestamp with time zone value. now() is a traditional PostgreSQL equivalent to transaction_timestamp().

• pozs: Maybe worth to highlight clock_timestamp()
• nkuhta: Clock_timestamp and statement_timestamp do what I want. Thanks!
• PainIsAMaster: @deceze Thank you so much, I was trying to update a timestamp (variable containing now()) defined in a WHILE..LOOP in my plpgsql Procedure and it was NOT updating, so my condition never failed (infinite loop). I didn't know Timestamps were linked to Transactions. So I added a "commit" after the update of time (var_time := now(); commit;) and everything works fine now