Oracle 21c中VARCHAR2类型时长列求和方案实现

问题背景 #

在Oracle 21c环境下，t_video表的video_duration字段为VARCHAR2类型，需要对该字段存储的时长数据进行求和统计。

表结构与测试数据 #

CREATE TABLE t_video 
(
    video_id NUMBER NOT NULL ENABLE, 
    video_duration VARCHAR2(30 BYTE), 
    object_video VARCHAR2(1000 BYTE), 

    CONSTRAINT T_VIDEO_PK PRIMARY KEY ( VIDEO_ID )
); 

INSERT INTO t_video (video_id, video_duration, object_video)
VALUES (1,'00:12:20','song'); 
INSERT INTO t_video (video_id, video_duration, object_video)   
VALUES (2,'02:50:30','film');

尝试的解决方法 #

1. 分列统计时、分、秒 #

通过截取字符串拆分时、分、秒后分别求和，再转换为小时单位：

-- 分列统计时、分、秒
SELECT
    SUM(to_char(substr(video_duration, - 8, 2))) AS hours,
    SUM(to_char(substr(video_duration, - 5, 2))) / 60 AS minutes,
    SUM(to_char(substr(video_duration, - 2, 2))) / 60 / 60 AS seconds 
FROM 
    t_video;

2. 合并为单列统计总时长 #

将时、分、秒统一转换为小时单位后求和，支持按用户分组及汇总：

-- 合并为单列统计总时长
SELECT
    id_user,
    SUM(ROUND(h1 + h2 + h3, 2)) AS total_hours   
FROM 
    (SELECT                                  
         id_user,                               
         to_char(substr(video_duration, -8, 2)) AS h1,                                  
         to_char(substr(video_duration, -5, 2)) / 60 AS h2,                                 
         to_char(substr(video_duration, -2, 2)) / 60 / 60 AS h3
     FROM                                
         t_video)
GROUP BY
    ROLLUP(id_user);

最终实现方案（转换为INTERVAL类型统计） #

将字符串时长转换为时间间隔类型后提取秒数求和，再转换为DAY TO SECOND格式展示总时长，支持分组汇总：

SELECT id_user,
    CAST(SUM(
    EXTRACT(HOUR FROM INTERVAL '0 ' || video_duration) * 60 * 60 + 
    EXTRACT(MINUTE FROM INTERVAL '0 ' || video_duration) * 60 + 
    EXTRACT(SECOND FROM INTERVAL '0 ' || video_duration)) *
    INTERVAL '1' SECOND AS 
    INTERVAL DAY(1) TO 
    SECOND(0)) AS 
    total_duration
 FROM
 t_video
 GROUP BY ROLLUP(id_user);

注：原代码直接对VARCHAR2字段使用EXTRACT会报错，需先将字符串转换为INTERVAL类型，上述代码补充了该转换逻辑。

统计结果 #

RESULT
id_user     total_duration
-------     ------------------
10          +241 07:39:36
------------------------------
20          +075 13:40:00
------------------------------
NULL        +316 21:19:36

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