如何解决不同时间间隔查询Postgres关系型数据库时的数据不匹配问题

核心问题：时区双重转换错误 #

你遇到的数值不匹配问题90%以上是时区处理错误导致的，具体逻辑如下：

• 时间换算基础：你本机时区为UTC+3，意味着本地时间 = UTC时间 + 3小时，对应的UTC时间 = 本地时间 - 3小时。你在本地时间x、x+20min执行两次全表count，对应的数据库服务端UTC时间分别为x-3h、x+20min-3h。
• 错误触发逻辑：DBeaver默认会使用你本机的时区作为数据库会话时区，你在查询中手动输入的x-3h、x+20min-3h这类不带时区标识的时间值，Postgres会默认按照会话时区（UTC+3）解析，再转换为UTC时间和存储的updated_at比对，相当于你手动减了3小时之后，数据库又自动帮你减了一次3小时，最终查询的时间区间比你预期的早了整整3小时，自然只有少量历史记录命中。

其他次要影响因素 #

• 时间边界处理问题：你查询条件中使用了<= (x+20min)-3hrs，如果新增记录的updated_at精度到毫秒，刚好超出你写的边界值会被漏算，建议将<=改为<更合理。
• 存在记录删除操作：两次全表count的差值为「20分钟内新增记录数 - 20分钟内删除记录数」，如果这段时间有数据删除，差值本身就会小于实际新增的记录数。
• 存在老记录更新：updated_at命中区间的记录包含「新增记录」和「这段时间被修改的老记录」，如果没有老记录被修改、也没有数据删除，数值才会和两次count的差值匹配。

1. 先验证会话时区，执行SQL确认返回值是否为你本地的UTC+3时区：

show timezone;

2. 不需要手动做时区偏移，直接用带时区转换的写法查询即可：

-- 这里的:x替换为你第一次执行count的本地时间
select count(id) from table1 
where updated_at >= :x::timestamptz AT TIME ZONE 'UTC'
and updated_at < (:x + interval '20 min')::timestamptz AT TIME ZONE 'UTC';

3. 也可以直接修改DBeaver的会话时区为UTC，后续查询直接用UTC时间写条件即可，不需要额外转换。

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