如何将文本类型时间字段转换为date/time格式以计算间隔时长

以下针对不同主流数据库环境给出可直接使用的转换及计算方案：

MySQL 环境 #

• 用STR_TO_DATE()函数将HH:MM格式的文本转为时间类型，再通过TIMESTAMPDIFF()计算时间间隔，示例为计算分钟间隔：

SELECT 
  start,
  end,
  TIMESTAMPDIFF(MINUTE, STR_TO_DATE(start, '%H:%i'), STR_TO_DATE(end, '%H:%i')) AS duration_minutes
FROM dbo_table;

• 如需以小时为单位，将分钟结果除以60即可：

SELECT TIMESTAMPDIFF(MINUTE, STR_TO_DATE(start, '%H:%i'), STR_TO_DATE(end, '%H:%i'))/60 AS duration_hours FROM dbo_table;

SQL Server 环境 #

• 直接用CAST()/CONVERT()转成TIME类型，再用DATEDIFF()计算间隔，注意end是SQL Server保留关键字，作为字段名需要加方括号转义：

SELECT
  start,
  [end],
  DATEDIFF(MINUTE, CAST(start AS TIME), CAST([end] AS TIME)) AS duration_minutes
FROM dbo_table;

PostgreSQL 环境 #

• 用::time语法完成文本到时间类型的转换，通过时间差计算结果：

SELECT
  start,
  end,
  EXTRACT(EPOCH FROM (end::time - start::time))/60 AS duration_minutes
FROM dbo_table;

跨天场景修正 #

如果存在跨天排班（比如start为22:00，end为次日06:00），上述计算会得到负数，可通过判断逻辑修正，以SQL Server为例：

SELECT
  CASE WHEN DATEDIFF(MINUTE, CAST(start AS TIME), CAST([end] AS TIME)) >=0 
       THEN DATEDIFF(MINUTE, CAST(start AS TIME), CAST([end] AS TIME))
       ELSE DATEDIFF(MINUTE, CAST(start AS TIME), CAST([end] AS TIME)) + 1440 END AS duration_minutes
FROM dbo_table

其中1440为一天的总分钟数，其他数据库可参照该逻辑做对应调整。

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