SQLite中字符串格式日期使用ORDER BY按真实日期正确排序问题

你遇到的排序错误是因为字符串按ASCII码逐位比较，单个数字的月/日没有补零的情况下，10的首字符1比3小，就会出现2020.1.10排在2020.1.3前面的问题。下面是两种可直接落地的解决方案：

方案1：查询时动态转换（无需修改存量数据） #

把日期字符串中的.替换为SQLite日期函数可识别的-，再用date()函数转换为日期类型后排序即可，SQL写法如下：

SELECT *
FROM 你的表名
ORDER BY date(replace(date_time, '.', '-'));

如果担心date()函数的兼容性，也可以拆分年月日分别转为整数排序，效果完全一致：

SELECT *
FROM 你的表名
ORDER BY
  -- 提取年份转整数排序
  CAST(substr(date_time, 1, instr(date_time, '.') - 1) AS INTEGER),
  -- 提取月份转整数排序
  CAST(substr(date_time, instr(date_time, '.') + 1, instr(substr(date_time, instr(date_time, '.') + 1), '.') - 1) AS INTEGER),
  -- 提取日期转整数排序
  CAST(substr(date_time, instr(date_time, '.', instr(date_time, '.') + 1) + 1) AS INTEGER);

方案2：修改存储格式（长期使用效率更高） #

如果后续还要频繁按日期查询、排序，建议把存量日期字符串统一修改为补零+横杠分隔的格式，比如2020.2.1改成2020-02-01，这种格式的字符串本身的排序规则就和真实日期顺序完全一致，后续直接按原字段排序即可，不需要额外计算：

-- 改完格式后的查询语句，性能更高
SELECT *
FROM 你的表名
ORDER BY date_time;

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