PostgreSQL中的date,time函数的注意点

最新推荐文章于 2025-01-06 21:23:31 发布

原创最新推荐文章于 2025-01-06 21:23:31 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Post过热SQL #now #cruurent #ateile

PostgreSQL 专栏收录该内容

211 篇文章

订阅专栏

本文详细介绍了PostgreSQL中与当前日期和时间相关的SQL标准函数，包括CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP、LOCALTIME和LOCALTIMESTAMP等。这些函数在事务开始时提供一致的时间戳，确保同一事务中的多个操作具有相同的时间标记。

摘自：
https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

postgres=# begin;
BEGIN
postgres=# 
postgres=# 
postgres=# select now(); ---->>> now()返回当前事务开始时的date和time。
              now              
-------------------------------
 2019-09-15 10:31:55.165102-04
(1 row)

postgres=# select current_timestamp; ---->>> current_timestamp返回当前事务开始时的date和time。
       current_timestamp       
-------------------------------
 2019-09-15 10:31:55.165102-04
(1 row)

postgres=# 
postgres=# 
postgres=# 
postgres=# select current_timestamp;
       current_timestamp       
-------------------------------
 2019-09-15 10:31:55.165102-04 ---->>>>返回结果同上一条命令。
(1 row)

postgres=# select statement_timestamp();---->>>>返回当前SQL语句的开始执行时的date和time
      statement_timestamp      
-------------------------------
 2019-09-15 10:34:33.631838-04
(1 row)

postgres=# select current_date; ---->>>>返回当前事务开始时的date
 current_date 
--------------
 2019-09-15
(1 row)

postgres=# select current_time;---->>>>返回当前事务开始时的time(带时区)
    current_time    
--------------------
 10:31:55.165102-04
(1 row)

postgres=# select localtime;---->>>>返回当前事务开始时time(不带时区，因为是local)
    localtime    
-----------------
 10:31:55.165102
(1 row)

postgres=# select localtimestamp;---->>>>返回当前事务开始时localtimestamp(不带时区，因为是local)
       localtimestamp       
----------------------------
 2019-09-15 10:31:55.165102
(1 row)

postgres=# select now();---->>> now()返回当前事务开始时的date和time。
              now              
-------------------------------
 2019-09-15 10:31:55.165102-04
(1 row)

postgres=# select statement_timestamp();---->>>>返回当前SQL语句的开始执行时的date和time
      statement_timestamp      
-------------------------------
 2019-09-15 10:37:16.115313-04
(1 row)

postgres=# 
postgres=#

摘自：
https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

9.9.4. Current Date/Time

PostgreSQL provides a number of functions that return values related to the current date and time. These SQL-standard functions all return values based on the start time of the current transaction:

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

CURRENT_TIME and CURRENT_TIMESTAMP deliver values with time zone; LOCALTIME and LOCALTIMESTAMP deliver values without time zone.

CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, and LOCALTIMESTAMP can optionally take a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field. Without a precision parameter, the result is given to the full available precision.

Some examples:

SELECT CURRENT_TIME;
Result: 14:39:53.662522-05

SELECT CURRENT_DATE;
Result: 2001-12-23

SELECT CURRENT_TIMESTAMP;
Result: 2001-12-23 14:39:53.662522-05

SELECT CURRENT_TIMESTAMP(2);
Result: 2001-12-23 14:39:53.66-05

SELECT LOCALTIMESTAMP;
Result: 2001-12-23 14:39:53.662522

Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications within the same transaction bear the same time stamp.