PostgreSQL每日一贴--时间日期函数与事务

      CURRENT_TIME,CURRENT_DATE,CURRENT_TIMESTAMP,LOCALTIMESTAMP等函数把时间当做当前事务的开始返回；在事务运行的时候， 它们的数值并不改变。 PostgreSQL认为这是一个特性：目的是为了允许一个事务在"当前" 时间上有连贯的概念，这样在同一个事务离得多个修改可以有同样的时间戳。

      如果要希望事务中时间是改变的，需要使用clock_timestamp或timeofday函数

测试代码如下

CREATE OR REPLACE FUNCTION TEST_CURRENT_TIME() RETURNS double precision AS $$DECLARE    start_time time;end_time time;time_consuming double precision ;BEGIN    time_consuming := -1;    select current_time into start_time;perform pg_sleep(3);select current_time into end_time;select round(EXTRACT(EPOCH from start_time)-EXTRACT(EPOCH from end_time)) into time_consuming;return time_consuming;END;$$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION TEST_CLOCK_TIMESTAMP() RETURNS double precision AS $$DECLARE    start_time timestamp;end_time timestamp;time_consuming double precision;BEGIN    time_consuming := -1;    select clock_timestamp() into start_time;perform pg_sleep(3);select clock_timestamp() into end_time;select round(EXTRACT(EPOCH from end_time)-EXTRACT(EPOCH from start_time)) into time_consuming;return time_consuming;END;$$ LANGUAGE plpgsql;

测试结果如下

postgres=> select * from TEST_CURRENT_TIME();
 test_current_time 
-------------------
                 0
(1 row)


postgres=> select * from TEST_CLOCK_TIMESTAMP();
 test_clock_timestamp 
----------------------
                    3
(1 row)


postgres=>