sql - oracle中如何计算char数据类型的求和时间

Question

我正在尝试创建一个查询，以对来自 2 个不同表的 oracle 中数据类型为 char 的记录求和。我有这样的表:

table 1:

name             time_rent
---------------- -----------
james            07:30

name 和time 都是char

table 2:

name             time_expired
---------------- -----------
james            18:30

name 和time 都是char

如何使用总和来显示记录以获得总时间(持续时间)，是否可能？。我使用 Oracle Database 18c 企业版在 Oracle Live SQL 中对其进行编码和测试。

Answer 1

WITH x AS (
  SELECT t1.time_rent AS t1,
         t2.time_rent AS t2,
         ((SUBSTR(t1.time_rent,1,2) * 3600) + (SUBSTR(t1.time_rent,4,2) * 60)
         + (SUBSTR(t2.time_rent,1,2) * 3600) + (SUBSTR(t2.time_rent,4,2) * 60)) AS t 
   FROM table1 t1 
   INNER JOIN Table2 t2 
   ON t1.name=t2.name
  ),
y AS(
   SELECT t1,
          t2,
          numtodsinterval(t,'second') AS t
          FROM x
)
SELECT T1,
       T2,
       (EXTRACT(day FROM t) * 24 + EXTRACT(hour FROM t) ||':' || 
       EXTRACT(minute FROM t) ||':' || EXTRACT(second FROM t)) AS duration 
       FROM y

输出

T1      T2      DURATION
07:30   18:30   26:0:0

现场演示

http://sqlfiddle.com/#!4/aaa519/12