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](https://wiki.postgresql.org/wiki/Don't_Do_This)