SQLite按时间段查询异常：按小时筛选返回0行问题排查

嘿，你这个问题的核心其实是数据类型不匹配搞的鬼，我来给你拆解清楚：

strftime('%H', TimeStamp) 函数返回的是字符串类型的小时值（比如你的示例时间戳2017-12-22T20:57:02.188Z会返回 "20"），但你在 BETWEEN 条件里用的是数字 20 和 21。在SQLite中，字符串和数字直接比较时，隐式转换的结果并不会符合你的预期——简单说就是字符串 "20" 和数字 20 不会被判定为相等，所以你的查询根本匹配不到任何数据。

修正方案1：用字符串做比较 #

把 BETWEEN 里的数字改成字符串形式，和 strftime 返回的类型保持一致就行：

SELECT TimeStamp,
       strftime('%Y',TimeStamp) as "Year",
       strftime('%m',TimeStamp) as "Month",
       strftime('%d',TimeStamp) as "Day",
       strftime('%M',TimeStamp) as "Minute",
       strftime('%H', TimeStamp) as "Hours"
FROM temp
WHERE strftime('%H', TimeStamp) BETWEEN '20' AND '21';

修正方案2：把字符串转成数字 #

用 CAST 函数把 strftime 返回的小时字符串转换成整数，再和数字比较也能解决问题：

SELECT TimeStamp,
       strftime('%Y',TimeStamp) as "Year",
       strftime('%m',TimeStamp) as "Month",
       strftime('%d',TimeStamp) as "Day",
       strftime('%M',TimeStamp) as "Minute",
       strftime('%H', TimeStamp) as "Hours"
FROM temp
WHERE CAST(strftime('%H', TimeStamp) AS INTEGER) BETWEEN 20 AND 21;

额外优化小建议 #

如果你的 TimeStamp 字段是SQLite支持的TEXT/REAL/INTEGER时间类型，直接基于完整时间范围筛选可能更高效（尤其是当这个字段有索引的时候）：

SELECT TimeStamp,
       strftime('%Y',TimeStamp) as "Year",
       strftime('%m',TimeStamp) as "Month",
       strftime('%d',TimeStamp) as "Day",
       strftime('%M',TimeStamp) as "Minute",
       strftime('%H', TimeStamp) as "Hours"
FROM temp
WHERE TimeStamp BETWEEN '2017-12-22T20:00:00Z' AND '2017-12-22T21:59:59.999Z';

完全匹配你时间戳的格式就行，这样既准确又能利用索引提升查询速度。

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