SQLite中如何将人类可读日期转UNIX时间戳进行范围查询？

原语句错误原因 #

你写的SQL语句无法工作的核心问题是类型不匹配：

• 左边的date([time@timestamp])会把REAL类型的时间戳转换为YYYY-MM-DD格式的字符串（比如'2022-11-29'）
• 右边的CAST(strftime('%s', '2022-11-29') AS REAL)得到的是该日期0点对应的UNIX时间戳数值（比如1669723200.0）

字符串和数值直接比较会导致逻辑错误，自然查不到预期数据。

两种正确的查询方案 #

方案1：将时间戳转成日期字符串，与输入日期直接比较

利用SQLite的date()函数可以直接解析REAL类型时间戳的特性，直接把数据库中的时间戳转成日期字符串，和输入的日期字符串对比：

SELECT * FROM data 
WHERE date([time@timestamp]) BETWEEN '2022-11-29' AND '2022-11-30';

注意：这个条件会包含2022-11-29和2022-11-30两天的所有数据。如果你的需求是只包含到2022-11-30的23:59:59，这个写法完全适用。

方案2：将输入日期转成时间戳，与数据库REAL列直接比较

这种方式更高效（可利用time@timestamp列的索引），需要注意：输入的2022-11-30默认对应当天0点的时间戳，如果要包含当天所有数据，应该把结束时间设为下一天的0点：

SELECT * FROM data 
WHERE [time@timestamp] BETWEEN 
  CAST(strftime('%s', '2022-11-29') AS REAL) 
  AND 
  CAST(strftime('%s', '2022-12-01') AS REAL);

这样就能覆盖2022-11-29 00:00:00到2022-11-30 23:59:59.999的所有数据，避免漏掉接近当天结束的毫秒级记录。

补充说明 #

• SQLite中strftime('%s', date_str)返回整数类型的秒级UNIX时间戳，转成REAL后可与你的毫秒级时间戳（比如1669729394.792）兼容比较。
• 如果需要精确到毫秒的日期转换，可使用strftime('%s.%f', date_str)，但日期范围查询用秒级精度已足够。

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