Postgres中如何计算两个存储为character varying类型的时间的差值

错误原因 #

你当前的写法报错是因为date类型需要包含年、月、日的完整日期格式输入，而你存储的16:00仅包含小时、分钟信息，不符合date类型的格式要求，所以类型转换失败。

解决方法 #

先将两个字符串字段转换为time类型，再做差值计算即可，不同数据库的适配写法如下：

• PostgreSQL 写法
  直接转换为time类型做差，默认返回interval类型的时间差：

SELECT
  time_departed::time - time_attended::time AS duration
FROM your_table_name;

如果需要得到小时、分钟的数值结果，可以通过时间戳转换提取：

-- 计算得到小时数，示例输入16:00、17:00返回1
SELECT
  EXTRACT(EPOCH FROM (time_departed::time - time_attended::time)) / 3600 AS duration_hours
FROM your_table_name;

• MySQL 写法
  先用STR_TO_DATE把字符串转成时间格式，再用时间差函数计算：

SELECT
  TIMEDIFF(STR_TO_DATE(time_departed, '%H:%i'), STR_TO_DATE(time_attended, '%H:%i')) AS duration
FROM your_table_name;

如果需要得到分钟/小时的数值结果：

-- 计算得到分钟数，示例输入16:00、17:00返回60
SELECT
  TIMESTAMPDIFF(MINUTE, STR_TO_DATE(time_attended, '%H:%i'), STR_TO_DATE(time_departed, '%H:%i')) AS duration_minutes
FROM your_table_name;

注意：

1. 示例中的your_table_name请替换为你实际的表名，不要直接使用table作为表名，它是SQL保留关键字，直接使用会触发语法错误。
2. 如果你的数据中存在跨天的情况（比如time_attended是23:00，time_departed是01:00），仅用时分计算会得到负数/不符合预期的结果，这种场景建议存储完整的带年月日的时间字段，避免计算偏差。

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