ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL DBA(112) - pgAdmin(Don't do this:Don't use timestamptz(0))

PostgreSQL DBA(112) - pgAdmin(Don't do this:Don't use timestamptz(0))

翻译 PostgreSQL 作者:husthxd 时间:2019-10-22 11:07:23 0 删除 编辑

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

Because it rounds off the fractional part rather than truncating it as everyone would expect. This can cause unexpected issues; consider that when you store now() into such a column, you might be storing a value half a second in the future.

原因是:
这样的定义会把小数部分舍入而不是大多数所期望的截断。

[local]:5432 pg12@testdb=# drop table if exists t_timestamp1;
NOTICE:  table "t_timestamp1" does not exist, skipping
DROP TABLE
Time: 37.935 ms
[local]:5432 pg12@testdb=# create table t_timestamp1(id int,time1 timestamptz(0),time2 timestamp);
CREATE TABLE
Time: 140.401 ms
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# insert into t_timestamp1 values(1,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP);
INSERT 0 1
Time: 44.471 ms
[local]:5432 pg12@testdb=# insert into t_timestamp1 values(2,now(),now());
INSERT 0 1
Time: 12.053 ms
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# select * from t_timestamp1;
 id |         time1          |           time2            
----+------------------------+----------------------------
  1 | 2019-10-22 10:54:13+08 | 2019-10-22 10:54:12.986034
  2 | 2019-10-22 10:54:13+08 | 2019-10-22 10:54:13.032517
(2 rows)
Time: 3.258 ms
[local]:5432 pg12@testdb=#

如案例所示,2019-10-22 10:54:12.986034会被舍入为2019-10-22 10:54:13+08,而不是2019-10-22 10:54:12+08。

参考资料
Don’t Do This

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

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

注册时间:2007-12-28

  • 博文量
    1417
  • 访问量
    3856297