PostgreSQL中含Timestamp字段的指定日期精准查询问题

这个问题很常见——当你的字段是timestamp类型时，直接用日期字符串做>=比较，会因为时间部分的存在或者日期格式解析歧义导致结果不符合预期。我来给你拆解原因和解决方案：

为什么你的查询返回了全部记录？ #

当你执行select * from books where bookdate >= '09-04-2018'时，PostgreSQL会把字符串'09-04-2018'隐式转换为timestamp类型，默认是当天的零点（09-04-2018 00:00:00）。但这里大概率存在日期格式解析的歧义：PostgreSQL默认的日期格式是YYYY-MM-DD，所以'09-04-2018'会被解析成2018-09-04 00:00:00，而不是你预期的2018-04-09。这样一来，>= 2018-09-04的条件自然会包含所有在这之后的记录（包括10-04-2018的记录），所以返回了全部5条。

正确的筛选方法 #

下面两种方法都能准确筛选出指定日期的所有记录，忽略时间部分：

方法1：使用时间范围查询（推荐，适合大表）

通过指定目标日期的起始零点和下一天的起始零点，用>=和<来限定范围：

SELECT * FROM books 
WHERE bookdate >= '2018-04-09 00:00:00' 
  AND bookdate < '2018-04-10 00:00:00';

• 优势：可以直接利用bookdate字段上的索引，查询效率很高，不会有性能问题。
• 注意：务必使用YYYY-MM-DD的标准日期格式，避免解析错误；如果必须用DD-MM-YYYY格式，要显式转换：

  SELECT * FROM books 
  WHERE bookdate >= TO_TIMESTAMP('09-04-2018', 'DD-MM-YYYY')
    AND bookdate < TO_TIMESTAMP('10-04-2018', 'DD-MM-YYYY');
  

方法2：转换为Date类型比较（简洁，适合小表）

把timestamp字段转换为date类型，直接和目标日期比较：

SELECT * FROM books 
WHERE bookdate::DATE = '2018-04-09';

或者用更规范的CAST写法：

SELECT * FROM books 
WHERE CAST(bookdate AS DATE) = '2018-04-09';

• 优势：写法简洁直观，容易理解。
• 注意：如果bookdate字段有索引，直接转换字段会导致索引失效（因为数据库需要对每条记录做转换）。如果你的表数据量很大，建议要么用方法1，要么创建一个基于bookdate::DATE的函数索引：

  CREATE INDEX idx_books_bookdate_date ON books ((bookdate::DATE));
  

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