SQL中如何将字符串时间转换为可计算的小时数

核心思路 #

要么将字符串时间转换为时间类型后计算差值，要么直接拆分小时和分钟计算总小时数再相减。

情况1：字符串不带引号（或已去除引号） #

方法1：拆分小时和分钟计算总小时数

适用于所有支持字符串函数的数据库，无需依赖时间类型转换：

-- 计算精确到小数的小时差
SELECT 
  (SUBSTRING_INDEX(`End time`, ':', 1) + SUBSTRING_INDEX(`End time`, ':', -1)/60) 
  - (SUBSTRING_INDEX(`Start time`, ':', 1) + SUBSTRING_INDEX(`Start time`, ':', -1)/60) AS hour_diff
FROM your_table;

方法2：转换为时间类型后计算差值

根据不同数据库选择对应函数：

• MySQL：

  -- 获取整数小时差
  SELECT TIMESTAMPDIFF(HOUR, STR_TO_DATE(`Start time`, '%H:%i'), STR_TO_DATE(`End time`, '%H:%i')) AS hour_diff
  FROM your_table;
  
  -- 获取带小数的精确小时差
  SELECT TIMESTAMPDIFF(MINUTE, STR_TO_DATE(`Start time`, '%H:%i'), STR_TO_DATE(`End time`, '%H:%i'))/60 AS hour_diff
  FROM your_table;
  

• PostgreSQL：

  SELECT EXTRACT(EPOCH FROM (TO_TIMESTAMP("End time", 'HH24:MI') - TO_TIMESTAMP("Start time", 'HH24:MI')))/3600 AS hour_diff
  FROM your_table;
  

• SQL Server：

  -- 获取带小数的精确小时差
  SELECT DATEDIFF(MINUTE, CAST("Start time" AS TIME), CAST("End time" AS TIME))/60.0 AS hour_diff
  FROM your_table;
  

情况2：字符串包含双引号（如示例中的"8:00"） #

如果字段实际存储的字符串带有双引号，需要先去除引号再处理，用REPLACE函数即可：

-- 以MySQL为例，先去引号再转时间计算差值
SELECT TIMESTAMPDIFF(MINUTE, 
  STR_TO_DATE(REPLACE(`Start time`, '"', ''), '%H:%i'), 
  STR_TO_DATE(REPLACE(`End time`, '"', ''), '%H:%i')
)/60 AS hour_diff
FROM your_table;

为什么CAST/TRIM没效果？ #

之前用CAST失败大概率是因为字符串包含双引号，TRIM只能去除空格，无法处理引号，导致时间类型转换失败。先通过REPLACE去掉引号后，再用CAST或时间转换函数就能正常工作。

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