PostgreSQL 如何获取当前日期时间及注意事项

    在开发数据库应用或者调试代码时,经常需要获取系统的当前日期和时间,我们来看一下 PostgreSQL 中提供的相关函数。

    当前日期

    CURRENT_DATE

    CURRENT_DATE 函数用于获取数据库服务器的当前日期:

    
    postgres=# SELECT CURRENT_DATE;
     current_date 
    --------------
     2019-09-28
    (1 row)
    

    调用该函数时不需要在函数名后加括号。该日期是服务器的日期,不是客户端的日期。

    当前事务开始时间

    以下函数可以用于获取数据库服务器的当前时间:

    
    CURRENT_TIME
    CURRENT_TIME(precision)
    LOCALTIME
    LOCALTIME(precision)
    
    CURRENT_TIMESTAMP
    CURRENT_TIMESTAMP(precision)
    LOCALTIMESTAMP
    LOCALTIMESTAMP(precision)

    CURRENT_TIME、LOCALTIME、CURRENT_TIMESTAMP、LOCALTIMESTAMP

    前面 4 个函数用于获取时间,后面 4 个函数用于获取时间戳;CURRENT_TIME 和 CURRENT_TIMESTAMP 包含时区信息,LOCALTIME 和 LOCALTIMESTAMP 不包含时区信息。precision 用于指定小数秒的位数,取值为 0 – 6,默认为 6。

    
    postgres=# SELECT CURRENT_TIME, LOCALTIME, CURRENT_TIMESTAMP, LOCALTIMESTAMP;
      current_time  |  localtime  |    current_timestamp    |    localtimestamp    
    --------------------+-----------------+-------------------------------+----------------------------
     12:20:50.602412+08 | 12:20:50.602412 | 2019-09-28 12:20:50.602412+08 | 2019-09-28 12:20:50.602412
    (1 row)
    
    postgres=# SELECT CURRENT_TIME(3), LOCALTIME(3), CURRENT_TIMESTAMP(3), LOCALTIMESTAMP(3);
     current_time  | localtime  |   current_timestamp   |   localtimestamp   
    -----------------+--------------+----------------------------+-------------------------
     12:28:03.547+08 | 12:28:03.547 | 2019-09-28 12:28:03.547+08 | 2019-09-28 12:28:03.547
    (1 row)
    

    注意:上面所有的函数,包括 CURRENT_DATE,返回的都是当前事务开始的时间。在同一个事务期间,多次调用相同的函数将会返回相同的值,结果不会随着时间增加。这一点与其他数据库的实现可能不同。

    以下示例使用 pg_sleep 函数暂停 3 秒再次获取当前时间:

    
    postgres=# BEGIN;
    BEGIN
    postgres=# SELECT CURRENT_TIMESTAMP;
        current_timestamp    
    -------------------------------
     2019-09-28 12:43:57.075609+08
    (1 row)
    
    postgres=# SELECT pg_sleep(3);
     pg_sleep 
    ----------
    (1 row)
    
    postgres=# SELECT CURRENT_TIMESTAMP;
        current_timestamp    
    -------------------------------
     2019-09-28 12:43:57.075609+08
    (1 row)
    
    postgres=# COMMIT;
    COMMIT

    在事务中两次获取的时间相同。

    当前语句开始时间

    PostgreSQL 还提供了其他获取时间的函数:

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

    transaction_timestamp()

    transaction_timestamp() 等价于 CURRENT_TIMESTAMP,但是作用更加明确。

    statement_timestamp()

    statement_timestamp() 返回当前语句的开始时间,更准确地说,应该是接收到客户端最新命令的时间。statement_timestamp() 和 transaction_timestamp() 对于事务中的第一个命令返回的结果相同,但随后再执行 statement_timestamp() 将会返回不同的值。

    
    postgres=# BEGIN;
    BEGIN
    postgres=# SELECT statement_timestamp();
       statement_timestamp   
    -------------------------------
     2019-09-28 13:11:14.497135+08
    (1 row)
    
    postgres=# SELECT pg_sleep(3);
     pg_sleep 
    ----------
    (1 row)
    
    postgres=# SELECT statement_timestamp();
       statement_timestamp   
    -----------------------------
     2019-09-28 13:11:17.5141+08
    (1 row)
    
    postgres=# COMMIT;
    COMMIT

    两次执行结果之间相差了 3 秒左右。

    当我们在存储过程(Stored Procedure)中进行调试时,通常需要打印不同语句消耗的时间;此时就需要使用 statement_timestamp(),而不能使用 CURRENT_TIMESTAMP 或者 transaction_timestamp():

    
    CREATE OR REPLACE sp_test
    ...
    DECLARE
     lts_systimestamp timestamp;
    BEGIN;
     lts_systimestamp := statement_timestamp();
     ...
     RAISE NOTICE 'Step 1 take time: %', statement_timestamp() - lts_systimestamp;
     ...
    END;
    

    clock_timestamp()

    clock_timestamp() 返回当前实际的时间,即使在同一个 SQL 语句中也可能返回不同的值:

    
    postgres=# SELECT clock_timestamp() FROM generate_series(1,10);
        clock_timestamp    
    -------------------------------
     2019-09-28 13:18:55.659778+08
     2019-09-28 13:18:55.659786+08
     2019-09-28 13:18:55.659788+08
     2019-09-28 13:18:55.65979+08
     2019-09-28 13:18:55.659791+08
     2019-09-28 13:18:55.659793+08
     2019-09-28 13:18:55.659795+08
     2019-09-28 13:18:55.659797+08
     2019-09-28 13:18:55.659799+08
     2019-09-28 13:18:55.659801+08
    (10 rows)

    查询语句在 1 秒钟内返回了 10 条记录,但是每条记录产生的时间都不相同。

    timeofday()

    timeofday() 是 PostgreSQL 中一个历史遗留函数。它与 clock_timestamp() 一样返回当前实际时间,但是返回类型是一个格式化的字符串,而不是 timestamp with time zone:

    
    postgres=# SELECT timeofday() FROM generate_series(1,10);
           timeofday       
    -------------------------------------
     Sat Sep 28 13:23:05.068541 2019 CST
     Sat Sep 28 13:23:05.068570 2019 CST
     Sat Sep 28 13:23:05.068577 2019 CST
     Sat Sep 28 13:23:05.068584 2019 CST
     Sat Sep 28 13:23:05.068591 2019 CST
     Sat Sep 28 13:23:05.068598 2019 CST
     Sat Sep 28 13:23:05.068605 2019 CST
     Sat Sep 28 13:23:05.068612 2019 CST
     Sat Sep 28 13:23:05.068619 2019 CST
     Sat Sep 28 13:23:05.068626 2019 CST
    (10 rows)

    now()

    now() 是 PostgreSQL 中与 transaction_timestamp() 等价的一个传统函数,同一个事务中的结果不会改变:

    
    postgres=# BEGIN;
    BEGIN
    postgres=# SELECT now();
           now       
    -------------------------------
     2019-09-28 13:27:26.831492+08
    (1 row)
    
    postgres=# SELECT pg_sleep(3);
     pg_sleep 
    ----------
    (1 row)
    
    postgres=# SELECT now();
           now       
    -------------------------------
     2019-09-28 13:27:26.831492+08
    (1 row)
    
    postgres=# COMMIT;
    COMMIT

    另外,所有的日期/时间数据类型都支持使用字面值’now’指定当前日期和时间(当前事务开始时间)。因此,以下语句效果相同:

    
    SELECT CURRENT_TIMESTAMP;
    SELECT now();
    SELECT TIMESTAMP 'now'; -- 不要用于字段的 DEFAULT 值

    顺便说一下,PostgreSQL 还提供了其他几个特殊的日期和时间字面值:

    
    -- SELECT timestamp 'epoch', timestamp 'today', timestamp 'tomorrow', timestamp 'yesterday', TIME 'allballs';
    postgres=# SELECT DATE 'epoch', DATE 'today',DATE 'tomorrow', DATE 'yesterday', TIME 'allballs';
      date  |  date  |  date  |  date  |  time  
    ------------+------------+------------+------------+----------
     1970-01-01 | 2019-09-28 | 2019-09-29 | 2019-09-27 | 00:00:00
    (1 row)

    以上函数分别返回 UTC 1970 年 1 月 1 日零点、今天午夜、明天午夜、昨天午夜以及 UTC 零点。

    延迟执行

    以下函数可以用于延迟服务器进行的操作:

    
    pg_sleep(seconds)
    pg_sleep_for(interval)
    pg_sleep_until(timestamp with time zone)

    pg_sleep 将当前会话的进行暂停指定的秒数。seconds 的类型为 double precision,所以支持小数秒。我们在面前使用了该函数。

    pg_sleep_for 执行一个延迟的时间间隔,通常用于指定一个较大的延迟。

    pg_sleep_until 可以用于指定一个进程的唤醒时间。

    以下示例分别暂停 1.5 秒、5 分钟以及直到明天 3 点:

    
    SELECT pg_sleep(1.5);
    SELECT pg_sleep_for('5 minutes');
    SELECT pg_sleep_until('tomorrow 03:00');
    

    暂停时间的精度取决于不同平台的实现,通常可以达到 0.01 秒。延迟效果最少会满足指定的值,但有可能由于其他因素导致更长,例如服务器负载过高。尤其对于 pg_sleep_until,不能保证在完全准确的指定时间唤醒进程,但是也不会提前唤醒。

    注意:使用这些延迟函数时,确保当前会话没有锁定过多的资源;否则,其他会话将会一直等待,导致系统性能的下降。

    以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持lingkb。