Oracle中清理非整点异常时间值方法及删除语句无效排查

为什么你的DELETE语句没生效？ #

主要有两个核心问题：

1. 错误的类型处理方式：如果DATETIME是Oracle的DATE或TIMESTAMP类型，直接用LIKE做字符串匹配是不靠谱的。Oracle会对日期/时间类型做隐式转换，但转换后的字符串格式不一定是你看到的23-FEB-18 11.42.41.065000000 AM（这只是客户端显示格式），导致你的匹配模式根本找不到目标数据。
2. 匹配模式的精度错误：你的模式%00.00.00000%和整点时间的实际格式不匹配。整点时间的秒和毫秒部分是.00.000000000，而你的模式只写了5个零，长度不够，自然匹配不到任何整点数据，反过来也就删不掉非整点数据。

正确清理非整点数据的方法 #

Oracle提供了专门的日期截断函数TRUNC()，可以精准地将时间值截断到整点，我们只需要判断原时间是否等于截断后的整点时间，就能筛选出非整点数据。

步骤1：先验证要删除的数据（强烈建议先做这一步，避免误删）

执行以下查询，确认哪些是非整点数据：

SELECT DATETIME 
FROM table_name 
WHERE DATETIME != TRUNC(DATETIME, 'HH');

如果返回的结果符合你要删除的非整点记录，再执行删除操作。

步骤2：执行删除语句

DELETE FROM table_name 
WHERE DATETIME != TRUNC(DATETIME, 'HH');

• 对于DATE类型：TRUNC(DATETIME, 'HH')会把时间截断到当前小时的整点（比如23-FEB-18 11:42:41会变成23-FEB-18 11:00:00）。
• 对于TIMESTAMP类型：这个函数同样适用，会保留毫秒部分为全零（比如23-FEB-18 11.42.41.065000000 AM会变成23-FEB-18 11.00.00.000000000 AM）。

额外提醒

• 删除数据前一定要做好备份，或者在测试环境先验证逻辑。
• 如果你的DATETIME是带时区的TIMESTAMP WITH TIME ZONE类型，需要确保截断操作考虑时区，比如TRUNC(DATETIME AT TIME ZONE 'UTC', 'HH')，根据你的实际时区调整。

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