如何在SQLite中从table1表的varchar(255)类型time列提取总小时数？

嘿，我懂你遇到的困扰了——用datetime()或者常规字符串函数直接处理这种带分隔符的格式确实不太顺手。别担心，咱们可以用SQLite的内置字符串函数一步步拆分计算，不需要额外启用正则扩展（后面也会补充正则的方法供你参考）。

核心思路拆解 #

你的time列格式是星期几|开始时间-结束时间，咱们需要先把时间区间拆出来，再拆分开始/结束时间，最后计算每个时间段的时长并求和。

1. 提取时间区间部分：先定位|的位置，把后面的开始-结束字符串截取出来：

   substr(time, instr(time, '|') + 1) AS time_range
   

2. 拆分开始与结束时间：从time_range里用'-'拆分出两个时间点：

   • 开始时间：substr(time_range, 1, instr(time_range, '-') - 1)
   • 结束时间：substr(time_range, instr(time_range, '-') + 1)

3. 计算单段时长：把时间拆成小时和分钟，计算差值（分钟转成小时的小数形式）：

   • 小时差：结束小时数 - 开始小时数
   • 分钟差：(结束分钟数 - 开始分钟数)/60.0
   • 单段总时长：小时差 + 分钟差

完整SQL查询语句 #

把上面的步骤整合起来，直接计算总小时数的语句如下：

SELECT 
  SUM(
    (CAST(substr(end_time, 1, 2) AS INTEGER) - CAST(substr(start_time, 1, 2) AS INTEGER)) +
    (CAST(substr(end_time, 4, 2) AS INTEGER) - CAST(substr(start_time, 4, 2) AS INTEGER))/60.0
  ) AS total_hours
FROM (
  SELECT 
    substr(substr(time, instr(time, '|') + 1), 1, instr(substr(time, instr(time, '|') + 1), '-') - 1) AS start_time,
    substr(substr(time, instr(time, '|') + 1), instr(substr(time, instr(time, '|') + 1), '-') + 1) AS end_time
  FROM table1
);

关于正则方法的补充 #

如果你确实想用正则处理，需要注意SQLite默认没有内置正则函数，得先加载sqlite3_regexp扩展（不同环境加载方式不同，比如命令行用.load ./regexp）。加载后可以用regexp_replace快速提取时间：

-- 提取开始时间
regexp_replace(time, '.*\|(\d{2}:\d{2})-\d{2}:\d{2}', '\1') AS start_time
-- 提取结束时间
regexp_replace(time, '.*\|\d{2}:\d{2}-(\d{2}:\d{2})', '\1') AS end_time

不过这种方法依赖扩展支持，不如内置函数通用，所以优先推荐前面的方案。

用你提供的测试数据跑这个查询，结果会是24.0（8+6+10），完全符合预期~

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