SQLite DateTime列范围查询失效及性能下降问题求解

问题根因 #

SQLite采用动态类型系统，声明的DATETIME类型仅为类型亲和性，不会强制约束存储格式，实际存储的时间值会根据写入格式自动转为TEXT、REAL或INTEGER类型。
你遇到的查询结果异常，核心原因是TS字段存储格式不规范：从示例数据可看到，日期和时间之间存在2个多余空格，直接进行字符串比较时，逐位ASCII对比的结果和时间实际先后顺序不一致，导致符合时间要求的记录被误过滤。
使用datetime()函数转换后可得到正确结果的原因是函数会自动忽略格式差异，将输入转为标准时间值再比较，但函数包裹字段会导致查询无法命中TS字段上的索引，只能全表扫描，因此性能下降明显。 #

解决方案 #

最优方案（兼顾可读性与性能） #

• 第一步：批量修正TS字段存储格式，统一转为标准的YYYY-MM-DD HH:MM:SS字符串格式

UPDATE TICK SET TS = strftime('%Y-%m-%d %H:%M:%S', TS);

• 第二步：为TS字段创建索引（如未创建）

CREATE INDEX IF NOT EXISTS idx_tick_ts ON TICK(TS);

• 第三步：后续查询直接使用原生字段比较即可，标准时间字符串的字典序与时间顺序完全一致，筛选结果正确且可命中索引，性能最优

SELECT * FROM TICK t WHERE ts BETWEEN '2017-01-25 21:44:13' AND '2017-03-21 16:35:14';

高性能可选方案 #

如果需要更高的查询性能，可将TS字段存储为INTEGER类型的Unix时间戳（秒级或毫秒级），数字比较性能优于字符串，只需要在查询时将起止时间转为对应时间戳即可：

-- 转换TS为Unix秒级时间戳
UPDATE TICK SET TS = strftime('%s', TS);
-- 对应查询语句
SELECT * FROM TICK t WHERE ts BETWEEN strftime('%s', '2017-01-25 21:44:13') AND strftime('%s', '2017-03-21 16:35:14');

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