Как рассчитать сумму времени с типом данных char в оракуле

Question

Я пытаюсь создать запрос для суммирования записи с типом данных char в оракуле из 2 разных таблиц. У меня есть таблица, как это:

table 1:

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

name а также time Чарс

table 2:

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

name а также time Чарс

Как отобразить запись, используя сумму для получения общего времени (продолжительности), это возможно? Я написал код и протестировал его в Oracle Live SQL с помощью Oracle Database 18c Enterprise Edition.

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/

Answer 2

Важный совет для вас: пожалуйста, не храните временные интервалы в виде строк (CHAR/ VARCHAR2). Это затрудняет выполнение операций над ними при написании запросов и неэффективно работает с большими наборами данных.

Oracle предоставляет вам два типа данных именно для таких интервальных записей в базе данных, которые вы хотите сохранить.

ИНТЕРВАЛ

INTERVAL YEAR [(year_precision)] TO MONTH- Хранит период времени в годах и месяцах

INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds)]- Хранит период времени в днях, часах, минутах и ​​секундах

Второй тип полезен для хранения требуемых значений какINTERVAL .. HOUR to MINUTE,

Например:-INTERVAL '07:30' HOUR TO MINUTE

Итак, операция суммирования была бы простоtime_rent + time_expired, который мог бы сделать это намного проще работать. Теперь, поскольку вы сохранили их как символы, Oracle предоставляет функции для вашего спасения:

TO_DSINTERVAL-TO_DSINTERVALпреобразует строку символовCHAR, VARCHAR2, NCHAR, or NVARCHAR2 тип данных к 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 формат. Вы можете использовать EXTRACT функция для извлечения любого компонента из интервала (день. час, минута, секунда...) и изменение запроса для отображения в нужном формате.

демонстрация