CODEWITHSUNDEEP
sqloracle-databasedatetime
sadek920
sadek920

Reputation: 3

Sum column in data type varchar2 oracle 21c

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);

I converted the time to HH24 only and got the required result, which is as follows:

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


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

Upvotes: 0

Views: 107

Answers (2)

Schwern
Schwern

Reputation: 165318

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.

Demonstration.

Upvotes: 0

MT0
MT0

Reputation: 168361

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:

VIDEO_ID TOTAL_DURATION
1 +000000000 00:12:20.000000000
2 +000000000 02:50:30.000000000
null +000000000 03:02:50.000000000

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);
VIDEO_ID TOTAL_DURATION
1 +0 00:12:20
2 +0 02:50:30
null +0 03:02:50

fiddle

Upvotes: 2

Related Questions