PostgreSQL 时间函数分类与特性

KingbaseES 时间函数有两大类：返回事务开始时间和返回语句执行时的时间。具体函数看以下例子：

1、返回事务开始时的时间

以下函数返回事务开始的时间（通过 begin .. end 两次调用结果相同）：now, current_time , transaction_timestamp 返回的都是事务开始的时间。

test=# select now(),sys_sleep(1),now();
              NOW              | SYS_SLEEP |              NOW              
-------------------------------+-----------+-------------------------------
 2022-03-02 13:57:41.702597+08 |           | 2022-03-02 13:57:41.702597+08
(1 row)

test=# select current_time ,sys_sleep(1),current_time;
    CURRENT_TIME    | SYS_SLEEP |    CURRENT_TIME    
--------------------+-----------+--------------------
 14:01:26.031228+08 |           | 14:01:26.031228+08
(1 row)

test=# select transaction_timestamp(),sys_sleep(1),transaction_timestamp();
     TRANSACTION_TIMESTAMP     | SYS_SLEEP |     TRANSACTION_TIMESTAMP     
-------------------------------+-----------+-------------------------------
 2022-03-02 14:01:31.756111+08 |           | 2022-03-02 14:01:31.756111+08
(1 row)

2、返回函数执行时的时间

clock_timestamp ，timeofday 返回的是函数 执行时刻的时间

test=# select clock_timestamp(),sys_sleep(1),clock_timestamp();
        CLOCK_TIMESTAMP        | SYS_SLEEP |        CLOCK_TIMESTAMP        
-------------------------------+-----------+-------------------------------
 2022-03-02 14:05:44.657881+08 |           | 2022-03-02 14:05:45.659408+08
(1 row)

test=# select timeofday(),sys_sleep(1),timeofday();
              TIMEOFDAY              | SYS_SLEEP |              TIMEOFDAY              
-------------------------------------+-----------+-------------------------------------
 Wed Mar 02 14:05:52.292714 2022 CST |           | Wed Mar 02 14:05:53.293870 2022 CST
(1 row)

3、返回语句的执行时间

statement_timestamp  比较特殊：

test=# select statement_timestamp(),sys_sleep(1),statement_timestamp();
statement_timestamp | sys_sleep | statement_timestamp
-------------------------------+-----------+-------------------------------
2021-09-13 17:00:23.635612+08 | | 2021-09-13 17:00:23.635612+08
(1 row)

test=# begin;
BEGIN
test=# select statement_timestamp();
      statement_timestamp      
-------------------------------
 2021-09-13 17:04:14.442012+08
(1 row)

test=# select statement_timestamp();
      statement_timestamp      
-------------------------------
 2021-09-13 17:04:20.638240+08
(1 row)

test=# commit;

4、来看几个函数的稳定性

        PRONAME        | PROVOLATILE 
-----------------------+-------------
 clock_timestamp       | v
 now                   | s
 statement_timestamp   | s
 timeofday             | v
 transaction_timestamp | s
(5 rows)

从函数稳定性也可以看出 clock_timestamp , timeofday 是volatile ， 同一条语句多次调用必须多次执行。