Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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'); 
1 rows affected

INSERT INTO t_video (video_id, video_duration, object_video) VALUES (2,'02:50:30','film');
1 rows affected

INSERT INTO t_video (video_id, video_duration, object_video) VALUES (3,'12:50:30','film');
1 rows affected

-- 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));
3 rows affected

-- 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;
select * from t_video;


VIDEO_ID
OBJECT_VIDEO
VIDEO_DURATION


1
song
+00 00:12:20.000000

2
film
+00 02:50:30.000000

3
film
+00 12:50:30.000000


select extract(hour from video_duration) from t_video;


EXTRACT(HOURFROMVIDEO_DURATION)


0

2

12

VIDEO_ID	OBJECT_VIDEO	VIDEO_DURATION
1	song	+00 00:12:20.000000
2	film	+00 02:50:30.000000
3	film	+00 12:50:30.000000