基于数据集起止时间计算时间间隔的SQL函数选型咨询

不同SQL数据库有各自的内置函数来处理时间间隔计算，以下是主流数据库的具体实现，均支持跨年、跨月、跨日的时间场景：

MySQL/MariaDB #

• 使用TIMESTAMPDIFF()函数，指定计算单位后直接得到整数间隔值，语法：TIMESTAMPDIFF(单位, 开始时间, 结束时间)，支持的单位包括SECOND、MINUTE、HOUR、DAY、MONTH、YEAR等。
• 如果需要精确到小数的时长（比如带小数的小时），可以先计算总秒数再转换：

-- 计算跨年夜的精确小时数
SELECT TIMESTAMPDIFF(SECOND, '2023-12-31 23:00:00', '2024-01-01 02:30:00') / 3600 AS exact_hour_diff;
-- 返回结果：3.5

PostgreSQL #

• 直接用时间戳减法即可得到interval类型的间隔结果，也可以用EXTRACT()提取总秒数后转换为所需单位：

-- 得到原始间隔（03:30:00）
SELECT '2024-01-01 02:30:00'::timestamp - '2023-12-31 23:00:00'::timestamp AS time_diff;

-- 转换为精确小时数
SELECT EXTRACT(EPOCH FROM ('2024-01-01 02:30:00'::timestamp - '2023-12-31 23:00:00'::timestamp)) / 3600 AS exact_hour_diff;
-- 返回结果：3.5

另外AGE(结束时间, 开始时间)函数也能得到同样的interval结果，用法和减法一致。

SQL Server #

• 使用DATEDIFF()函数获取整数间隔，语法：DATEDIFF(单位, 开始时间, 结束时间)，支持的单位涵盖年、月、日、小时、秒等。
• 要获取精确小数时长，同样通过秒数转换：

-- 获取整数小时数（结果为3）
SELECT DATEDIFF(HOUR, '2023-12-31 23:00:00', '2024-01-01 02:30:00') AS hour_diff;

-- 获取精确小时数（结果为3.5）
SELECT DATEDIFF(SECOND, '2023-12-31 23:00:00', '2024-01-01 02:30:00') / 3600.0 AS exact_hour_diff;

Oracle #

• 通过日期减法得到天数差值，再乘以对应系数转换为所需单位（比如乘以24得到小时数）：

SELECT (TO_DATE('2024-01-01 02:30:00', 'YYYY-MM-DD HH24:MI:SS') - TO_DATE('2023-12-31 23:00:00', 'YYYY-MM-DD HH24:MI:SS')) * 24 AS exact_hour_diff
FROM DUAL;
-- 返回结果：3.5

注意：确保你的起止时间字段是datetime、timestamp这类时间类型，而非字符串，否则需要先做类型转换。

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