ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL DBA(111) - pgAdmin(Don't do this:Don't use CURRENT_TIME)

PostgreSQL DBA(111) - pgAdmin(Don't do this:Don't use CURRENT_TIME)

原创 PostgreSQL 作者:husthxd 时间:2019-10-21 11:49:48 0 删除 编辑

no zuo no die系列,来自于pg的wiki。
这一节的内容是:不要使用current_time。
理由是:

1.It returns a value of type timetz, for which see the previous entry.
2.Use whichever of these is appropriate.

原因是:
1.该函数会返回timetz类型,而该类型已废弃(应使用timestamptz)。
2.有更合适的函数可以使用

CURRENT_TIMESTAMP or now() if you want a timestamp with time zone,
LOCALTIMESTAMP if you want a timestamp without time zone,
CURRENT_DATE if you want a date,
LOCALTIME if you want a time

current_time的使用

[local]:5432 pg12@testdb=# select * from pg_proc where proname = 'current_time';
(0 rows)
Time: 4.634 ms
[local]:5432 pg12@testdb=# \df current_time
                       List of functions
 Schema | Name | Result data type | Argument data types | Type 
--------+------+------------------+---------------------+------
(0 rows)
Time: 1.083 ms
[local]:5432 pg12@testdb=# \d current_time
Did not find any relation named "current_time".
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# select * from current_time;
    current_time    
--------------------
 11:46:54.895344+08
(1 row)

从pg_proc和psql的df命令找不到该current_time。

相关的替代函数包括
1.CURRENT_TIMESTAMP或者now()
这是带时区的timestamp

[local]:5432 pg12@testdb=# \df now
                             List of functions
   Schema   | Name |     Result data type     | Argument data types | Type 
------------+------+--------------------------+---------------------+------
 pg_catalog | now  | timestamp with time zone |                     | func
(1 row)
[local]:5432 pg12@testdb=# select * from pg_proc where proname = 'now';
-[ RECORD 1 ]---+-----
oid             | 1299
proname         | now
pronamespace    | 11
proowner        | 10
prolang         | 12
procost         | 1
prorows         | 0
provariadic     | 0
prosupport      | -
prokind         | f
prosecdef       | f
proleakproof    | f
proisstrict     | t
proretset       | f
provolatile     | s
proparallel     | s
pronargs        | 0
pronargdefaults | 0
prorettype      | 1184
proargtypes     | 
proallargtypes  | 
proargmodes     | 
proargnames     | 
proargdefaults  | 
protrftypes     | 
prosrc          | now
probin          | 
proconfig       | 
proacl          | 
Time: 4.780 ms
[local]:5432 pg12@testdb=# select now();
              now              
-------------------------------
 2019-10-21 11:51:40.708556+08
(1 row)
Time: 1.860 ms
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# \df current_timestamp
                       List of functions
 Schema | Name | Result data type | Argument data types | Type 
--------+------+------------------+---------------------+------
(0 rows)
[local]:5432 pg12@testdb=# select * from pg_proc where proname = 'current_timestamp';
(0 rows)
Time: 3.014 ms
[local]:5432 pg12@testdb=# select * from current_timestamp;
      current_timestamp       
------------------------------
 2019-10-21 11:52:34.31964+08
(1 row)
Time: 2.246 ms
[local]:5432 pg12@testdb=# select current_timestamp;
       current_timestamp       
-------------------------------
 2019-10-21 11:52:37.363806+08
(1 row)
Time: 1.827 ms
[local]:5432 pg12@testdb=#

2.LOCALTIMESTAMP
这是不带时区的timestamp

[local]:5432 pg12@testdb=# \df localtimestamp
                       List of functions
 Schema | Name | Result data type | Argument data types | Type 
--------+------+------------------+---------------------+------
(0 rows)
[local]:5432 pg12@testdb=# select localtimestamp;
       localtimestamp       
----------------------------
 2019-10-21 11:53:43.376968
(1 row)
Time: 1.821 ms
[local]:5432 pg12@testdb=#

3.CURRENT_DATE
返回当前日期(年月日)

[local]:5432 pg12@testdb=# \df current_date
                       List of functions
 Schema | Name | Result data type | Argument data types | Type 
--------+------+------------------+---------------------+------
(0 rows)
[local]:5432 pg12@testdb=# select current_date;
 current_date 
--------------
 2019-10-21
(1 row)
Time: 1.871 ms
[local]:5432 pg12@testdb=#

4.LOCALTIME
不带时区的当前时间(时分秒微秒)

[local]:5432 pg12@testdb=# \df localtime
                       List of functions
 Schema | Name | Result data type | Argument data types | Type 
--------+------+------------------+---------------------+------
(0 rows)
[local]:5432 pg12@testdb=# select localtime;
   localtime    
----------------
 11:54:45.38508
(1 row)
Time: 2.081 ms
[local]:5432 pg12@testdb=#

参考资料
Don’t Do This

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-2660760/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。

注册时间:2007-12-28

  • 博文量
    1416
  • 访问量
    3855849