Oracle 19c DATE字段范围查询差一错误问题咨询

这个问题我之前排查过好几次，核心原因就是Oracle的DATE类型是包含时分秒的，但你用的这些数据库工具默认只显示日期部分，把时间细节给藏起来了。

为什么会出现这种查询差异？ #

• 你那条abc字段显示为2000-01-01的记录，实际存储的肯定不是2000-01-01 00:00:00，而是带有非零的时分秒（比如2000-01-01 08:30:15或者哪怕是2000-01-01 00:00:01）。
• 当你写abc <= DATE '2000-01-01'时，这个DATE字面量默认是2000-01-01 00:00:00，所以任何时分秒大于0的2000-01-01记录都不满足这个条件，自然查不出来。
• 而DATE '2000-01-01' + interval '1' day等价于2000-01-02 00:00:00，只要是2000-01-01当天的任何时间（不管时分秒是多少），都会满足abc < 2000-01-02 00:00:00，所以这条记录就被查出来了。

怎么验证这个猜想？ #

你可以用TO_CHAR函数把完整的时间格式打出来，就能看到隐藏的时分秒：

SELECT TO_CHAR(abc, 'YYYY-MM-DD HH24:MI:SS') AS full_datetime FROM t WHERE abc < DATE '2000-01-01' + INTERVAL '1' DAY;

执行后你肯定会发现这条记录的时间部分不是00:00:00。

怎么解决或者避免以后踩坑？ #

• 如果业务只需要匹配日期部分，推荐用范围查询（不会破坏索引）：

  SELECT abc FROM t WHERE abc >= DATE '2000-01-01' AND abc < DATE '2000-01-01' + INTERVAL '1' DAY;
  

  这种写法既准确匹配当天所有时间的记录，又能利用abc字段上的索引（如果有的话）。
• 要是你习惯用截断的方式，也可以用TRUNC，但注意如果abc有索引，TRUNC(abc)会导致索引失效：

  SELECT abc FROM t WHERE TRUNC(abc) <= DATE '2000-01-01';
  

• 另外可以调整工具的显示设置：比如在Oracle SQL Developer里，你可以修改DATE类型的默认显示格式，让它展示完整的时分秒，以后就不会被“表面现象”误导了。

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