Postgres中BETWEEN语句时间戳范围包含性异常问题排查

问题原因 #

你的ts字段存储的时间戳包含亚秒（毫秒/微秒）级精度，你查询时写的整秒时间和实际存储值不匹配，导致范围查询结果不符合预期。

具体现象解释 #

• 当你执行WHERE ts BETWEEN '2021-08-01 14:55:00Z' AND '2021-08-01 14:55:00Z'时，表中对应14:55的记录实际存储值类似2021-08-01 14:55:00.123456Z，大于查询条件里的整秒时间2021-08-01 14:55:00Z，不满足等于条件，因此无返回结果。
• 当你执行WHERE ts BETWEEN '2021-08-01 14:55:00Z' AND '2021-08-01 14:56:00Z'时，14:55的记录（14:55:00.xxxZ）小于上限14:56:00Z，可以被查询到；而14:56的记录实际是14:56:00.xxxZ，大于上限值，因此不会被返回。

你可以通过以下查询验证这个问题，查看结果中时间戳小数点后的亚秒级数值即可确认：

SELECT to_char(ts, 'YYYY-MM-DD HH24:MI:SS.USTZ') 
FROM t 
WHERE ts BETWEEN '2021-08-01 14:55:00Z' AND '2021-08-01 14:56:00Z'

解决办法 #

方案1：截断时间戳后比较 #

将字段值截断到整秒再和查询条件匹配：

-- 单时间点查询
WHERE date_trunc('second', ts) = '2021-08-01 14:55:00Z'
-- 范围查询写法
WHERE date_trunc('second', ts) BETWEEN '2021-08-01 14:55:00Z' AND '2021-08-01 14:56:00Z'

方案2：使用左闭右开的范围查询（更推荐） #

这种写法不受时间戳精度影响，也不需要对字段做函数处理，查询性能更优：

-- 仅查询14:55整秒的所有记录
WHERE ts >= '2021-08-01 14:55:00Z' AND ts < '2021-08-01 14:55:01Z'
-- 查询14:55、14:56两分钟的所有记录
WHERE ts >= '2021-08-01 14:55:00Z' AND ts < '2021-08-01 14:57:00Z'

内容的提问来源于stack exchange，提问作者Thomas