My question was about the possibility of collecting the time column, especially since the data type is 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);

Then I tried and succeeded in solving the problem as follows:

-- code sum hours , minutes, seconds in three column
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;

-- code sum hours , minutes, seconds in one column
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);

CodePudding user response：

If you are storing times you can use the INTERVAL DAY(0) TO SECOND(0) data type (rather than strings) and then your query can be:

SELECT video_id,                               
       SUM(
         EXTRACT(HOUR   FROM video_duration) * 60 * 60
         EXTRACT(MINUTE FROM video_duration) * 60
         EXTRACT(SECOND FROM video_duration)
       ) * INTERVAL '1' SECOND AS total_duration
FROM   t_video
GROUP BY ROLLUP(video_id);

Which, for the sample data:

CREATE TABLE t_video 
(
  video_id       NUMBER NOT NULL ENABLE, 
  video_duration INTERVAL DAY(0) TO SECOND(0), 
  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, INTERVAL '00:12:20' HOUR TO SECOND,'song');

INSERT INTO t_video (video_id, video_duration, object_video)
VALUES (2, INTERVAL '02:50:30' HOUR TO SECOND,'film');

Outputs:

If you want to format it differently you can cast from the default INTERVAL DAY(9) TO SECOND(9) to an interval with smaller precision such as INTERVAL DAY(1) TO SECOND(0):

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

fiddle

CodePudding user response：

The best thing to do would be to change the type of the columns to interval day to second. Then you can use Oracle's built in time interval functions.

-- Make a new interval day to second column.
alter table t_video add video_duration_new interval day to second;

-- Translate your string durations into intervals.
-- to_dsinterval requires a day, so we add 0 days. This assumes all your video durations are all HH:MM:SS
update t_video set video_duration_new = to_dsinterval(concat('0 ', video_duration));

-- Drop the old column.
alter table t_video drop column video_duration;

-- Replace it with the interval column.
alter table t_video rename column video_duration_new to video_duration;

Now getting the number of hours is trivial using extract.

select extract(hour from video_duration) from t_video;

This can also be indexed for performance, and will check the values are formatted properly.