如何用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 Enterprise Edition在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

live demo

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

Answer 2

对您的重要建议：请不要将时间间隔存储为字符串(CHAR/ VARCHAR2(。它使他们在编写查询时更难实施并在大型数据集上执行降低。

oracle为您提供了两种数据类型的数据库中的两种数据类型，您希望存储。

Interval

INTERVAL YEAR [(year_precision)] TO MONTH - 在几年和几个月中存储一段时间

INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds)] - 在一段时间内存储 天，小时，分钟和秒

第二种类型对您很有用，将所需值存储为INTERVAL .. HOUR to MINUTE。

eg：- INTERVAL '07:30' HOUR TO MINUTE

因此，总和操作就是简单的time_rent + time_expired，这可能会使操作更加容易。现在，由于您将它们存储为角色，因此Oracle确实为您的救援提供了功能：

to_dsinterval - TO_DSINTERVAL将 CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype的字符串转换为 INTERVAL DAY TO SECOND。

因此，将DAY组件和秒组件(零(附加到您的时间列将有助于将它们转换为INTERVAL类型：TO_DSINTERVAL('000 '|| time_rent|| ':00')。

因此，您的最终查询看起来像。

WITH t
     AS (SELECT t1.name,
                  TO_DSINTERVAL('000 '||    time_rent|| ':00')
                + TO_DSINTERVAL('000 '|| time_expired|| ':00') AS intv
         FROM   table1 t1
                join table2 t2
                        ON t1.name = t2.name)
SELECT name,
       EXTRACT(day FROM intv) * 24 + EXTRACT(hour FROM intv) --hours
       || ':'
       || EXTRACT(minute FROM intv) as duration  -- minutes
FROM   t;  

由于加法操作使总持续时间超过24小时(26(，因此间隔将被视为1 day 2 hours。EXTRACT功能用于在所需的HH:MM格式中获取O/P。您可以使用EXTRACT函数从间隔(Day.hour，minute，exch(中检索任何组件，并修改以所需格式显示的查询。

demo