iostat中时间 PM pg时间函数

转载

archangle 2023-12-02 21:13:00

文章标签 iostat中时间 PM sql 数据库 postgresql 函数返回 文章分类 iOS 移动开发

在介绍PostgreSQL的时间函数之前，我们先来看一个问题：

bill=# select now(),pg_sleep_for('5 second'),now();
             now              | pg_sleep_for |             now              
------------------------------+--------------+------------------------------
 2020-04-28 10:21:33.77725+08 |              | 2020-04-28 10:21:33.77725+08
(1 row)

上面这个语句为什么中间延迟了5秒，但是两个now函数获取的时间还是一样的呢？如果你不知道什么原因，那么看完这篇文章你就能弄明白了:)

PostgreSQL中的日期/时间函数主要分为两类：一类是支持SQL标准的，另一类是非SQL标准功能的。

SQL标准函数：
sql标准的函数主要有以下几种：

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

CURRENT_DATE
current_date返回的是以yyyy-mm-dd格式的日期。

bill=# select current_date;
 current_date 
--------------
 2020-04-28
(1 row)

CURRENT_TIMESTAMP/CURRENT_TIMESTAMP(precision)
CURRENT_TIME/CURRENT_TIME(precision)
这些函数返回带时区的当前日期和时间。您可以选择precision参数控制时间精度。此精度舍入小数位数（毫秒）。

bill=# select CURRENT_TIMESTAMP;
       current_timestamp       
-------------------------------
 2020-04-28 10:30:29.982662+08
(1 row)

bill=# select CURRENT_TIMESTAMP(1);
    current_timestamp     
--------------------------
 2020-04-28 10:30:33.8+08
(1 row)

bill=# select CURRENT_TIME;
    current_time    
--------------------
 10:30:40.337608+08
(1 row)

bill=# select CURRENT_TIME(2);
  current_time  
----------------
 10:30:43.79+08
(1 row)

LOCALTIME/LOCALTIME(precision)
LOCALTIMESTAMP/LOCALTIMESTAMP(precision)
这些函数返回没有时区的当前日期和时间。您可以选择precision参数控制时间精度。此精度舍入小数位数（毫秒）。

bill=# select LOCALTIMESTAMP;
      localtimestamp       
---------------------------
 2020-04-28 10:31:32.52472
(1 row)

bill=# select LOCALTIMESTAMP(1);
    localtimestamp     
-----------------------
 2020-04-28 10:31:38.1
(1 row)

bill=# select LOCALTIME;
    localtime    
-----------------
 10:31:45.975489
(1 row)

bill=# select LOCALTIME(2);
  localtime  
-------------
 10:31:48.75
(1 row)

非SQL标准函数：

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

transaction_timestamp()和 statement_timestamp()
transaction_timestamp顾名思义返回的是事务的时间，而statement_timestamp函数返回的是语句的时间，区别如下：

bill=# begin;
BEGIN
bill=# select statement_timestamp(), transaction_timestamp();
      statement_timestamp      |     transaction_timestamp     
-------------------------------+-------------------------------
 2020-04-28 10:34:43.811452+08 | 2020-04-28 10:34:42.466875+08
(1 row)

bill=# select pg_sleep(5);
 pg_sleep 
----------
 
(1 row)

bill=# select statement_timestamp(), transaction_timestamp();
      statement_timestamp      |     transaction_timestamp     
-------------------------------+-------------------------------
 2020-04-28 10:34:59.887614+08 | 2020-04-28 10:34:42.466875+08
(1 row)

可以看到在同一个事务中，transaction_timestamp时间都是一样的，而statement_timestamp函数返回的时间是变化的。

clock_timestamp()
clock_timestamp函数返回当前时间的真实时间（clock时间），在同一个语句中返回的值是不同的，例如本文开头的例子：

bill=# select clock_timestamp(),pg_sleep(5),clock_timestamp();
        clock_timestamp        | pg_sleep |        clock_timestamp        
-------------------------------+----------+-------------------------------
 2020-04-28 10:39:08.964432+08 |          | 2020-04-28 10:39:13.965785+08
(1 row)

timeofday()
timeofday函数和clock_timestamp函数类似，但是timeofday返回的是文本格式的数据。

bill=# select clock_timestamp(), pg_typeof(clock_timestamp()), timeofday(), pg_typeof(timeofday());
       clock_timestamp        |        pg_typeof         |              timeofday              | pg_typeof 
------------------------------+--------------------------+-------------------------------------+-----------
 2020-04-28 10:40:16.13543+08 | timestamp with time zone | Tue Apr 28 10:40:16.135431 2020 CST | text
(1 row)

now()
now函数等效于transaction_timestamp，在同一个事务中执行的结果相同。

bill=# begin;
BEGIN
bill=# select now(), transaction_timestamp();
             now              |    transaction_timestamp     
------------------------------+------------------------------
 2020-04-28 10:41:22.71028+08 | 2020-04-28 10:41:22.71028+08
(1 row)

bill=# select pg_sleep(5);
 pg_sleep 
----------
 
(1 row)

bill=# select now(), transaction_timestamp();
             now              |    transaction_timestamp     
------------------------------+------------------------------
 2020-04-28 10:41:22.71028+08 | 2020-04-28 10:41:22.71028+08
(1 row)

看到这里你应该已经知道本文开头的那个问题的答案了吧！
可能你会被这些在同一个语句、同一个事务中返回值不同的函数弄晕了，但其实之所以这些函数返回的结果不同归根结底还是和函数的稳定性有关，例如now和clock_timestamp函数，为什么now函数在同一个事务中返回值一样，其实我们看一下这两个函数的稳定性就知道了。

bill=# \df+ now
                                                                                        List of functions
   Schema   | Name |     Result data type     | Argument data types | Type | Volatility | Parallel |  Owner   | Security | Access privileges | Language | Source code |       Description        
------------+------+--------------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+-------------+--------------------------
 pg_catalog | now  | timestamp with time zone |                     | func | stable     | safe     | postgres | invoker  |                   | internal | now         | current transaction time
(1 row)

bill=# \df+ clock_timestamp
                                                                                            List of functions
   Schema   |      Name       |     Result data type     | Argument data types | Type | Volatility | Parallel |  Owner   | Security | Access privileges | Language |   Source code   |    Description   
  
------------+-----------------+--------------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+-----------------+------------------
--
 pg_catalog | clock_timestamp | timestamp with time zone |                     | func | volatile   | safe     | postgres | invoker  |                   | internal | clock_timestamp | current clock tim
e
(1 row)

可以看出，now函数是stable类型的函数，在同一个事务中返回的结果都是一样的，而clock_timestamp函数是volatile类型的函数，所以在同一个事务中返回结果是不同的。
其它的函数也是类似，我们通过其稳定性就可以知道其使用场景了。

oracle兼容：
在oracle中我们经常会用到的日期函数就是sysdate函数了，那么在pg中该如何替换呢？
sysdate显示的是当前系统时间，因此，在长时间运行的事务中，如果有多个SYSDATE函数，则每个语句执行都会返回不同的时间,这个听起来和pg中的clock_timestamp函数很类似。
例如：

SQL> SET SERVEROUTPUT ON ;
BEGIN
DBMS_OUTPUT.PUT_LINE('Start : ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
dbms_lock.sleep(30);
DBMS_OUTPUT.PUT_LINE('End : ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
 /
Start : 2020-04-27 10:52:50
End : 2020-04-27 10:53:20

可以看到的确和clock_timestamp函数一样。看来，最佳匹配替代SYSDATE的PostgreSQL函数是clock_timestamp()。不过真的如此吗？
oracle中的SYSDATE函数还有一个很重要的特点：SYSDATE对于客户端或会话级时区设置，该功能不起作用!
那么pg中的clock_timestamp函数呢，可以看看下面的例子:

bill=# show timezone;
 TimeZone 
----------
 PRC
(1 row)

bill=# select clock_timestamp();
        clock_timestamp        
-------------------------------
 2020-04-28 10:55:58.072012+08
(1 row)
bill=# set timezone = 'UTC';
SET
bill=# select clock_timestamp();
        clock_timestamp        
-------------------------------
 2020-04-28 02:56:18.983945+00
(1 row)

clock_timestamp函数是和时区有关的！那么我们又该怎么办呢？这个时候我们只好在pg中使用udf自己定义一个sysdate函数来兼容oracle了，其实也很简单，只需要定义一个返回类型是TIMESTAMP WITHOUT TIME ZONE的函数就可以了，例如：

bill=# show timezone;
 TimeZone 
----------
 PRC
(1 row)

bill=# CREATE OR REPLACE FUNCTION sysdate()
bill-# RETURNS TIMESTAMP WITHOUT TIME ZONE
bill-# AS
bill-# $$
bill$#     SELECT clock_timestamp() AT TIME ZONE 'PRC';
bill$# $$
bill-# LANGUAGE sql;
CREATE FUNCTION

这样就完美解决了兼容oracle中sysdate函数的问题了～

验证：

bill=# select sysdate();
          sysdate           
----------------------------
 2020-04-28 11:01:17.345951
(1 row)

bill=# set timezone = 'UTC';
SET
bill=# select sysdate();
          sysdate           
----------------------------
 2020-04-28 11:01:30.404638
(1 row)

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。