SQLite如何按包含时间的日期范围查询数据？

你的SQL语句未达预期主要有两个核心问题：

1. 日期字符串比较逻辑错误：mm/dd/yyyy格式的字符串直接用>=/<=比较会出错，因为字符串是按字符顺序逐位对比的——比如'12/01/2022'（2022年12月1日）会被判定为大于'01/10/2023'（2023年1月10日），导致日期范围过滤完全失效。
2. 时间范围逻辑错误：原语句会筛选出日期范围内每一天的13:45-15:50数据，而非你需要的「从2022-01-10 13:45到2023-01-10 15:50」这个连续时间段的数据。

下面给出几种可行的解决方法：

方法1：合并日期时间列（最优方案） #

如果可以修改表结构，建议把date和time列合并为一个datetime类型的列（比如命名为record_datetime），这能从根源避免逻辑错误，还能提升查询效率：

SELECT * FROM testtable 
WHERE record_datetime >= '2022-01-10 13:45:00' 
  AND record_datetime <= '2023-01-10 15:50:00';

后续可以给这个列添加索引，进一步优化查询速度。

方法2：拼接日期时间后转换为datetime类型查询 #

如果无法修改表结构，可通过数据库内置函数将date和time拼接后转换为datetime类型再比较，不同数据库的写法如下：

MySQL/MariaDB #

SELECT * FROM testtable 
WHERE STR_TO_DATE(CONCAT(date, ' ', time), '%m/%d/%Y %H:%i') 
      BETWEEN '2022-01-10 13:45:00' AND '2023-01-10 15:50:00';

SQL Server #

SELECT * FROM testtable 
WHERE CAST(date AS DATETIME) + CAST(time AS DATETIME) 
      BETWEEN '2022-01-10 13:45:00' AND '2023-01-10 15:50:00';

PostgreSQL #

SELECT * FROM testtable 
WHERE TO_TIMESTAMP(CONCAT(date, ' ', time), 'MM/DD/YYYY HH24:MI') 
      BETWEEN '2022-01-10 13:45:00' AND '2023-01-10 15:50:00';

注意：这种方法会导致数据库无法使用date和time列的索引，数据量大时查询效率会降低，建议给拼接转换后的表达式创建计算列并添加索引。

方法3：用逻辑条件组合实现（高效无函数） #

如果不想用函数转换，可以通过拆分逻辑条件来实现正确的时间段过滤，同时规避日期字符串比较的问题：

SELECT * FROM testtable 
WHERE 
  -- 日期在起始和结束日期之间，所有时间都符合
  (STR_TO_DATE(date, '%m/%d/%Y') > '2022-01-10' AND STR_TO_DATE(date, '%m/%d/%Y') < '2023-01-10')
  -- 日期等于起始日期，时间>=起始时间
  OR (STR_TO_DATE(date, '%m/%d/%Y') = '2022-01-10' AND time >= '13:45')
  -- 日期等于结束日期，时间<=结束时间
  OR (STR_TO_DATE(date, '%m/%d/%Y') = '2023-01-10' AND time <= '15:50');

这里用STR_TO_DATE把日期字符串转换为日期类型后再比较，避免了字符串对比的逻辑错误，同时可以利用date列的索引（如果有的话），查询效率更高。

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