时间戳

首页 > 解决方案 > 在 NUMTODSINTERVAL() 中不能使用超过 90 分钟

sql - 在 NUMTODSINTERVAL() 中不能使用超过 90 分钟

问题描述

我需要创建一个比较日期的请求。像这样的东西：

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;

但是当 f.Duration 的值超过 90 分钟时，它会因错误 ORA-01481: invalid number format model Start time data而崩溃

标签： sqloracle

解决方案

您的duration专栏应该是 aNUMBER而不是 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
);

然后你可以使用：

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<>在这里摆弄

推荐阅读