PostgreSQL基于无UTC时区timestamp列查询昨日今日数据报错如何解决

错误原因 #

• 核心是PostgreSQL不支持timestamp without time zone类型直接和整数做减法运算，你的SQL里两处写法不符合语法规范：
  1. (omd."ScannedAt")-5：不能直接对无时区时间戳减整数，必须明确指定时间间隔单位（比如小时、天）
  2. Now()-1：now()返回的是带时区的时间戳（timestamp with time zone），同样不能直接减整数，且和ScannedAt的无时区类型不匹配，会有隐式转换风险

修正方案 #

首先确认你原始SQL的逻辑：-5应该是要将UTC标准的ScannedAt减5小时转换为你所在时区的时间，between Now()-1 and now()是要筛选最近24小时内的记录。符合该逻辑的修正SQL如下：

SELECT omd.*
FROM "OCRMetaDatas" omd
WHERE omd."ScannedAt" - INTERVAL '5 hours' 
BETWEEN (now()::timestamp without time zone) - INTERVAL '1 day' AND now()::timestamp without time zone
ORDER BY omd."ScannedAt" DESC;

如果你不需要做时区转换，仅要筛选UTC时间的昨日到今日范围内的记录，可以直接用更简洁的写法：

SELECT omd.*
FROM "OCRMetaDatas" omd
WHERE omd."ScannedAt"
BETWEEN (CURRENT_DATE - INTERVAL '1 day')::timestamp AND CURRENT_DATE + INTERVAL '1 day'
ORDER BY omd."ScannedAt" DESC;

注意事项 #

• PostgreSQL的所有时间运算都需要用INTERVAL关键字指定时间单位，支持hour、day、minute、month等单位，你可以根据自己的实际时间偏移需求调整参数
• 尽量保证比较符两边的字段类型一致，避免隐式转换导致的索引失效或者结果偏差

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