php - 当脚本工作时，Postgres now() 时间戳不会改变

Question

我的 PHP 脚本将运行几秒钟，我需要随时从 Postgres 数据库获取真实的时间戳。但是当我从 Postgres 获取 current_timestamp 时，它总是返回相同的值。

这是我的示例脚本(使用 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;

这是输出:

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 安装在本地机器上。为什么它总是在同一时间返回？如何获得实时性？

我使用了 now() 函数，得到了相同的结果。Postgres 版本:9.3.4 x64。 PHP版本:5.5.11

Answer 1

来自 TFM , 突出显示我的:

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().