sqlite3中使用STRFTIME转换时间格式查询无结果问题求助

• SQLite内置时间处理函数（STRFTIME/DATETIME/DATE/TIME）仅识别严格零填充的ISO-8601格式时间字符串，要求月、日、时、分、秒段均为2位长度，个位数必须补前导零，即必须匹配YYYY-MM-DD HH:MM:SS格式。传入的无零填充时间（比如2022-6-2 3:6:8、测试语句里的01:3:0）属于非法输入，函数会直接返回NULL，而BETWEEN条件中任意一侧为NULL时，判断结果永远为假，自然返回空结果。
• 原有查询语句存在冗余逻辑：对合法的标准时间字符串调用STRFTIME('%Y-%m-%d %H:%M:%S', ...)得到的结果本身就是SQLite认可的时间格式，外层嵌套DATETIME()没有任何实际作用，也不会修正非标准格式的输入。

方案1：程序端预格式化（推荐，性能最优） #

不要把格式修正逻辑下沉到SQL层，在程序接收到protobuf输出的时间字符串后，直接对年、月、日、时、分、秒段做前导零补全，拼成标准YYYY-MM-DD HH:MM:SS格式后再传入SQL。
处理完成后查询语句不需要嵌套任何时间函数，直接做字符串比较即可：

SELECT * FROM database WHERE DATE_TIME BETWEEN '2022-06-02 01:03:00' AND '2022-06-02 01:04:00';

这种写法的优势是可以直接命中DATE_TIME字段上的索引，数据量越大性能优势越明显。如果DATE_TIME字段存储的是整型EPOCH时间戳，直接在程序端把起止时间转成对应整型值做数值比较即可，完全不需要处理字符串格式，性能是所有方案里最高的。

方案2：SQL层补零转换（仅用于无法修改程序入参逻辑的场景） #

如果必须在SQL侧处理无零填充的非标准时间输入，需要先拆分时间字符串的各个分段，用PRINTF补前导零拼成合法格式后，再交给时间函数处理。示例写法如下：

SELECT * FROM database 
WHERE DATE_TIME BETWEEN 
  DATETIME(
    -- 提取年份
    SUBSTR(@start_time, 1, 4) || '-' ||
    -- 提取月份补零
    PRINTF('%02d', CAST(SUBSTR(@start_time, 6, INSTR(SUBSTR(@start_time,6), '-')-1) AS INTEGER)) || '-' ||
    -- 提取日期补零
    PRINTF('%02d', CAST(SUBSTR(SUBSTR(@start_time,6), INSTR(SUBSTR(@start_time,6), '-')+1, INSTR(SUBSTR(@start_time, INSTR(@start_time,' ')),' ')-1) AS INTEGER)) || ' ' ||
    -- 提取小时补零
    PRINTF('%02d', CAST(SUBSTR(@start_time, INSTR(@start_time,' ')+1, INSTR(SUBSTR(@start_time,INSTR(@start_time,' ')+1), ':')-1) AS INTEGER)) || ':' ||
    -- 提取分钟补零
    PRINTF('%02d', CAST(SUBSTR(SUBSTR(@start_time,INSTR(@start_time,' ')+1), INSTR(SUBSTR(@start_time,INSTR(@start_time,' ')+1), ':')+1, INSTR(SUBSTR(SUBSTR(@start_time,INSTR(@start_time,' ')+1), INSTR(SUBSTR(@start_time,INSTR(@start_time,' ')+1), ':')+1), ':')-1) AS INTEGER)) || ':' ||
    -- 提取秒数补零
    PRINTF('%02d', CAST(SUBSTR(SUBSTR(@start_time,INSTR(@start_time,' ')+1), INSTR(SUBSTR(@start_time,INSTR(@start_time,' ')+1), ':')+INSTR(SUBSTR(SUBSTR(@start_time,INSTR(@start_time,' ')+1), INSTR(SUBSTR(@start_time,INSTR(@start_time,' ')+1), ':')+1), ':')+1) AS INTEGER))
  )
  AND
  -- 结束时间按相同逻辑处理即可
  DATETIME('2022-06-02 01:04:00');

注意：禁止在DATE_TIME字段本身套用函数做格式转换，否则会触发全表扫描，数据量较大时查询延迟会急剧升高。

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