ITPub博客

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

PostgreSQL DBA(109) - pgAdmin(Don't do this:Don't use BETWEEN)

翻译 PostgreSQL 作者:husthxd 时间:2019-10-17 11:47:42 0 删除 编辑

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

BETWEEN uses a closed-interval comparison: the values of both ends of the specified range are included in the result.
This is a particular problem with queries of the form
SELECT FROM blah WHERE timestampcol BETWEEN ‘2018-06-01’ AND ‘2018-06-08’;
This will include results where the timestamp is exactly 2018-06-08 00:00:00.000000, but not timestamps later in that same day. So the query might seem to work, but as soon as you get an entry exactly on midnight, you’ll end up double-counting it.
Instead, do:
SELECT FROM blah WHERE timestampcol >= ‘2018-06-01’ AND timestampcol < ‘2018-06-08’

原因是between是闭合区间,在处理日期时会丢失精度,比如日期’2018-06-08’会认为是’2018-06-08 00:00:00.000000’而不是’2018-06-08 23:59:59.999999’,下面举例说明。

创建数据表并插入数据

[local]:5432 pg12@testdb=# drop table if exists t_between;
DROP TABLE
Time: 4.715 ms
[local]:5432 pg12@testdb=# create table t_between(id int,tz timestamptz);
CREATE TABLE
Time: 4.788 ms
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# insert into t_between values(1,CURRENT_TIMESTAMP);
INSERT 0 1
Time: 3.620 ms
[local]:5432 pg12@testdb=# insert into t_between values(2,now());
INSERT 0 1
Time: 2.319 ms
[local]:5432 pg12@testdb=# insert into t_between values(3,date_trunc('second',CURRENT_TIMESTAMP));
INSERT 0 1
Time: 2.542 ms
[local]:5432 pg12@testdb=# insert into t_between values(4,date_trunc('day',CURRENT_TIMESTAMP));
INSERT 0 1
Time: 2.766 ms
[local]:5432 pg12@testdb=# select * from t_between order by id;
 id |              tz               
----+-------------------------------
  1 | 2019-10-17 11:47:07.876236+08
  2 | 2019-10-17 11:47:07.881309+08
  3 | 2019-10-17 11:47:07+08
  4 | 2019-10-17 00:00:00+08
(4 rows)
Time: 1.760 ms

查询数据

[local]:5432 pg12@testdb=# select * from t_between where tz between'2019-10-16' and '2019-10-17';
 id |           tz           
----+------------------------
  4 | 2019-10-17 00:00:00+08
(1 row)
Time: 1.691 ms
[local]:5432 pg12@testdb=# select * from t_between where tz >= '2019-10-16'  and tz < '2019-10-17';
 id | tz 
----+----
(0 rows)
Time: 1.186 ms
[local]:5432 pg12@testdb=#

用between会把值为2019-10-17 00:00:00+08的数据输出,因此建议使用普通的比较符(>、<、=等)。

参考资料
Don’t Do This

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

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

注册时间:2007-12-28

  • 博文量
    1416
  • 访问量
    3855848