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

No zuo no die series, from wiki of pg.

This section is about: don't use between.

The reason is:

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 This will include results where the timestamp is exactly 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 TABLETime: 4.715 ms[local]:5432 pg12@testdb=# create table t_between(id int,tz timestamptz);CREATE TABLETime: 4.788 ms[local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# insert into t_between values(1,CURRENT_TIMESTAMP);INSERT 0 1Time: 3.620 ms[local]:5432 pg12@testdb=# insert into t_between values(2,now());INSERT 0 1Time: 2.319 ms[local]:5432 pg12@testdb=# insert into t_between values(3,date_trunc('second',CURRENT_TIMESTAMP));INSERT 0 1Time: 2.542 ms[local]:5432 pg12@testdb=# insert into t_between values(4,date_trunc('day',CURRENT_TIMESTAMP));INSERT 0 1Time: 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=#

Using between will output data with a value of 2019-10-17 00, so it is recommended to use normal comparators (>, <, =, etc.).

references

Don't Do This