Cannot use more than 90 minutes in NUMTODSINTERVAL()-CodePudding

I need to create a request that will compare the dates. Something like this:

SELECT f.Name,
       TO_CHAR(s.Film_Start_Time, 'hh24:mi'),
       TO_CHAR(f.Duration, 'hh24:mi'),
       f2.Name,
       TO_CHAR(s2.Film_Start_Time, 'hh24:mi'),
       TO_CHAR(f2.Duration, 'hh24:mi')
FROM   Schedule s
       JOIN Films f ON s.Film_Name = f.Name
       JOIN Schedule s2 ON s2.Film_Start_Time > s.Film_Start_Time
                       AND s2.Film_Start_Time < s.Film_Start_Time   NUMTODSINTERVAL(f.Duration, 'MINUTE')
       JOIN Films f2 ON s2.Film_Name = f2.Name
ORDER BY s.Film_Start_Time ASC;

But when value of f.Duration more than 90 minutes its crushed with error ORA-01481: invalid number format model Start time data

CodePudding user response：

Your duration column should be a NUMBER and not a DATE:

CREATE TABLE films (
    name             VARCHAR2(20 CHAR) NOT NULL,
    duration         NUMBER(4,0) NOT NULL,
    category         VARCHAR2(20 CHAR) NOT NULL,
    company          VARCHAR2(20 CHAR),
    country          VARCHAR2(20 CHAR),
    year_of_release  NUMBER
);

Then you can use:

SELECT f.Name,
       TO_CHAR(s.Film_Start_Time, 'hh24:mi') AS f1_start_time,
       TO_CHAR(s.film_start_time   NUMTODSINTERVAL(f.Duration, 'MINUTE'), 'hh24:mi')
         AS f1_end_time,
       f2.Name,
       TO_CHAR(s2.Film_Start_Time, 'hh24:mi') AS f2_start_time,
       TO_CHAR(s2.Film_start_time   NUMTODSINTERVAL(f2.Duration, 'MINUTE'), 'hh24:mi')
         AS f2_end_time
FROM   Schedule s
       JOIN Films f ON s.Film_Name = f.Name
       JOIN Schedule s2 ON s2.Film_Start_Time > s.Film_Start_Time
                       AND s2.Film_Start_Time < s.Film_Start_Time   NUMTODSINTERVAL(f.Duration, 'MINUTE')
       JOIN Films f2 ON s2.Film_Name = f2.Name
ORDER BY s.Film_Start_Time ASC;

db<>fiddle here