SQLite用INTEGER存储Unix时间戳查询无结果的原因与实现方案

本次查询无结果的根本原因 #

• 对SQLite内置函数存在误用：CURRENT_TIMESTAMP的默认返回值是ISO-8601格式的TEXT字符串，格式为YYYY-MM-DD HH:MM:SS，完全不是预期的Unix时间戳整数。
• 类型亲和转换失效：虽然你将datetime字段定义为INTEGER类型，按照SQLite的类型亲和规则，存入TEXT值时会尝试将文本转为数字，但带分隔符的日期字符串包含'-'、':'、空格等非数字字符，无法解析为合法整数，最终会以TEXT类型原样存储在字段中。
• 跨类型比较的规则陷阱：SQLite固定的值比较优先级为NULL < 数值类型（INTEGER/REAL） < TEXT字符串 < BLOB二进制。这类跨类型比较完全不会解析字符串内嵌的数字大小，只要属于TEXT类型，就会被判定为比任何整数都大，和字符串本身代表的实际时间没有关系。因此当表中datetime字段实际存储的是TEXT值时，你传入的整数查询参数无论多大，datetime <= 整数ts的判断永远为假，自然无法返回查询结果。

适配性能要求的Unix时间戳查询实现 #

你期望的整数直接比较逻辑在修正存储方式后完全可以实现，且能发挥最优查询性能：

• 存储端规则修正：插入数据时不再使用CURRENT_TIMESTAMP，改用strftime('%s', 'now')生成当前时刻的Unix整数秒。该函数返回纯数字形式的时间值，存入INTEGER字段时会自动转为整数类型，不存在非数字字符。
  修正后的插入语句示例：

  conn.execute("INSERT INTO data VALUES (strftime('%s', 'now'), 'hello')")
  

• 比较逻辑实现：当所有存储的datetime值为纯整数类型后，完全可以采用无额外转换的整数比较逻辑：
  • 查询时直接传入整数形式的时间戳即可，比较过程为整数与整数的直接数值比对，无任何类型转换开销。
  • 若为datetime字段建立普通索引，大数量级下可直接走索引扫描，查询效率达到最优，完全匹配你对性能的要求，无需任何字符串转换操作。
• 存量错误数据修正：如果之前已经误插入了TEXT格式的日期值，可以通过单条更新语句将存量值转为标准Unix整数时间戳：

  UPDATE data 
  SET datetime = CAST(strftime('%s', datetime) AS INTEGER) 
  WHERE typeof(datetime) = 'text'
  

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