溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
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
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
