如何在Oracle中使用数据类型char计算总和时间 [英] how to calculate sum time with data type char in oracle

问题描述

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

i am trying to create a query to sum record with data type char in oracle from 2 table different. I have table like this :

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中进行了编码和测试.

how to display record using sum for get total time (duration), is it possible?. I coding and tested it in Oracle Live SQL with Oracle Database 18c Enterprise Edition.

推荐答案

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

这篇关于如何在Oracle中使用数据类型char计算总和时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！