PostgreSQL事务中的时间

在同一个事务中，时间是不变的：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

postgres=# begin; BEGIN postgres=# select now(),current_time,current_timestamp,localtime,localtimestamp,transaction_timestamp();               now              |    current_time    |       current_timestamp       |    localtime    |       localtimestamp       |     transaction_timestamp     -------------------------------+--------------------+-------------------------------+-----------------+----------------------------+-------------------------------  2021-01-22 16:50:46.917081+08 | 16:50:46.917081+08 | 2021-01-22 16:50:46.917081+08 | 16:50:46.917081 | 2021-01-22 16:50:46.917081 | 2021-01-22 16:50:46.917081+08 (1 row)   postgres=# select pg_sleep_for('10 seconds');  pg_sleep_for --------------    (1 row)   postgres=# select now(),current_time,current_timestamp,localtime,localtimestamp,transaction_timestamp();               now              |    current_time    |       current_timestamp       |    localtime    |       localtimestamp       |     transaction_timestamp     -------------------------------+--------------------+-------------------------------+-----------------+----------------------------+-------------------------------  2021-01-22 16:50:46.917081+08 | 16:50:46.917081+08 | 2021-01-22 16:50:46.917081+08 | 16:50:46.917081 | 2021-01-22 16:50:46.917081 | 2021-01-22 16:50:46.917081+08 (1 row)   postgres=#

 

要想获得在事务中某个语句执行的时间，可以使用clock_timestamp()

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

postgres=# begin; BEGIN postgres=# select statement_timestamp(),clock_timestamp();       statement_timestamp      |        clock_timestamp        -------------------------------+-------------------------------  2021-01-22 16:54:13.433445+08 | 2021-01-22 16:54:13.433605+08 (1 row)   postgres=# select pg_sleep_for('10 seconds');  pg_sleep_for --------------    (1 row)   postgres=# select statement_timestamp(),clock_timestamp();      statement_timestamp      |        clock_timestamp        ------------------------------+-------------------------------  2021-01-22 16:54:29.92086+08 | 2021-01-22 16:54:29.921041+08 (1 row)   postgres=#