查询指定时间段记录时结果天数异常，如何获取准确结果数？

问题 #

需求是获取完整7天的记录，使用的查询语句如下：

SELECT id, entry_timestamp
FROM entries
WHERE created_on >= TO_DATE('2023-01-25', 'YYYY-MM-DD') at time zone 'UTC' - interval '7 days'
ORDER BY entry_timestamp

表中entry_timestamp是**timestamp without timezone类型列，created_on是date**类型列（使用TO_DATE()而非current_date是因为需要指定特定日期）。

执行查询时，得到的entry_timestamp结果仅来自最近6天；若将interval改为'8 days'，却得到10天的结果，请问是什么原因导致这种计算偏差？

原因与解决方法 #

问题核心是类型转换与时区处理的逻辑错误：

1. 你把date类型的TO_DATE('2023-01-25', 'YYYY-MM-DD')通过at time zone 'UTC'转换成了timestamp with time zone类型，再减去时间间隔后依然是带时区的时间戳。但用它和date类型的created_on做比较时，PostgreSQL会自动将带时区的时间戳转换为当前会话时区对应的date——这个转换过程会因为你会话时区和UTC的差异，导致实际筛选的起始日期和预期不符。

比如：如果你的会话时区是UTC-5（西五区），TO_DATE('2023-01-25', 'YYYY-MM-DD') at time zone 'UTC'是UTC时间2023-01-25 00:00，转换为西五区的日期是2023-01-24（西五区比UTC晚5小时，此时西五区还是24号的晚上19点）；减去7天的时间戳转换为西五区日期后是2023-01-17，此时筛选created_on >= 2023-01-17会包含17到25号的记录，和你遇到的偏差情况一致。

2. 既然created_on是date类型，完全不需要引入时区转换，直接对date类型做日期运算即可：

正确的查询语句 #

SELECT id, entry_timestamp
FROM entries
-- 直接对日期做减法，确保结果还是date类型
WHERE created_on >= TO_DATE('2023-01-25', 'YYYY-MM-DD') - INTERVAL '7 days'::DATE
ORDER BY entry_timestamp;

或者更简洁的写法：

SELECT id, entry_timestamp
FROM entries
WHERE created_on >= '2023-01-25'::DATE - 7
ORDER BY entry_timestamp;

这样运算后的结果是纯粹的date类型，不会因为时区转换产生偏移，能精准筛选出从2023-01-18到2023-01-25的完整7天记录（若要排除25号当天，可将>=改为>）。

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