数据库SQL中TIMESTAMP字段的时间片段筛选及反向匹配最优方法

做SQL查询的核心优先级：尽可能不对TIMESTAMP字段本身做函数/格式转换，一旦字段值被函数加工，原本建在字段上的索引就会完全失效，小表可能还感知不到性能差异，大表上全表扫描的代价会非常高。

两类场景的统一实现思路 #

不管是需要筛选出TIMESTAMP字段日期部分为某一值的记录，还是手头仅有yyyy-MM-dd格式的纯日期字符串、要匹配对应存储格式的TIMESTAMP记录，本质都是要命中目标自然日从00:00:00到次日00:00:00之间的所有时间戳值，统一用范围查询实现即可：

• 已知具体日期值时，直接写字面量边界（无额外计算开销，最直观）：

-- 例：匹配所有日期部分为2021-04-23的TIMESTAMP记录
WHERE ts_field >= '2021-04-23 00:00:00' 
  AND ts_field < '2021-04-24 00:00:00'

• 输入仅有纯日期字符串时，用数据库自带的日期函数自动拼接范围边界，不需要手动计算次日日期，减少人为出错概率：

-- 例：输入纯日期值'2021-03-04'，匹配所有对应日期的TIMESTAMP记录
WHERE ts_field >= CONCAT('2021-03-04', ' 00:00:00')
  AND ts_field < DATE_ADD('2021-03-04', INTERVAL 1 DAY)

常见低效写法的问题说明 #

• 不推荐字符串前缀匹配：很多人第一反应会写WHERE ts_field LIKE '2021-04-23%'，看起来逻辑通顺，但实际上数据库存储TIMESTAMP是按数值时间戳存的，要做LIKE匹配必须先把数值转成字符串，这一步隐式转换直接就把索引打废了；而且不同数据库的时间字符串格式不一定固定，比如部分环境默认输出是04/23/2021 14:17:46，这种写法直接就匹配不到任何数据。
• 不推荐对字段套日期函数：比如WHERE DATE(ts_field) = '2021-04-23'、WHERE TO_CHAR(ts_field, 'yyyy-MM-dd') = '2021-04-23'这类写法，本质是逐行计算字段值的日期部分，根本走不了索引，百万级以上的表查一次就要扫全表，性能比范围查询差好几个量级。
• 避开精度漏数的坑：不少人写范围的时候右边界会随手写ts_field <= '2021-04-23 23:59:59'，如果你的TIMESTAMP字段带毫秒、微秒精度，类似2021-04-23 23:59:59.789这种值就会被漏掉。正确的右边界直接用次日0点的开区间，不管字段精度到秒还是到微秒，都能完整覆盖，不会漏数。

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