Oracle SQL Hours Разница между датами в формате ЧЧ:ММ:СС

Предполагая, что столбцы уже являются датами, и поэтому преобразование в строки и из них бессмысленно, и что разница всегда будет меньше дня, вы можете сделать:

to_char(date '1970-01-01' + abs(stp.created_date - adhh.created_date), 'HH24:MI:SS')

Это дает разницу между датами в виде доли дня; убеждается, что он положительный через abs(); а затем прибавляет эту дробь к номинальной дате, которая соответствует полуночи. Затем он преобразует это в строку, глядя только на время.

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

-- CTEs to supply the two date/times
with stp (created_date) as (
  select cast(timestamp '2018-02-26 12:59:21' as date) from dual
),
adhh (created_date) as (
  select cast(timestamp '2018-02-26 12:59:32' as date) from dual
)
select to_char(date '1970-01-01' + abs(stp.created_date - adhh.created_date), 'HH24:MI:SS')
from stp cross join adhh;

TO_CHAR(
--------
00:00:11

Если разница может превышать день, но не год, вы можете изменить модель формата на что-то вроде 'FMDDD FMHH24:MI:SS', что в начале будет показывать полные дни.

Предполагая, что столбцы уже имеют значения DATE (если нет, я настоятельно рекомендую изменить их), вы можете преобразовать их в метку времени. Должно быть достаточно преобразовать только одно значение, но, конечно, вы можете преобразовать оба.

CAST(stp.created_date AS TIMESTAMP) - adhh.created_date AS dif

Результатом является значение INTERVAL DAY TO SECOND, имеющее фиксированный выходной формат DDD HH:MI:SS.FF. Чтобы получить желаемый формат, вы можете использовать

REGEXP_SUBSTR(CAST(stp.created_date AS TIMESTAMP) - adhh.created_date, '\d{2}:\d{2}:\d{2}') AS dif

Обратите внимание, что TO_CHAR не работает для интервалов. Таким образом, вы должны использовать REGEXP или извлекать компоненты с помощью EXTRACT(... FROM ...)

Вы можете преобразовать его в интервал, а затем просто использовать TO_CHAR для преобразования интервала в строку:

скрипт SQL

Настройка схемы Oracle 11g R2:

CREATE TABLE STP ( id INT, created_date DATE );
CREATE TABLE ADHH ( id INT, created_date DATE );

INSERT INTO STP  values ( 1, TIMESTAMP '2018-02-26 12:59:21' );
INSERT INTO STP  values ( 2, TIMESTAMP '2018-02-26 12:00:30' );
INSERT INTO ADHH values ( 1, TIMESTAMP '2018-02-26 12:59:32' );
INSERT INTO ADHH values ( 2, TIMESTAMP '2018-02-26 12:00:00' );

Запрос 1:

SELECT TO_CHAR(
         ( s.created_date - a.created_date ) DAY(1) TO SECOND(0)
       ) AS diff
FROM   STP s
       INNER JOIN ADHH a
       ON ( s.id = a.id )

Результаты:

|        DIFF |
|-------------|
| -0 00:00:11 |
| +0 00:00:30 |

Запрос 2. Если у вас нет компонента дней (т. е. даты всегда отличаются менее чем на 24 часа), просто замените подстроку 0:

SELECT REPLACE(
         ( s.created_date - a.created_date ) DAY(1) TO SECOND(0),
         '0 '
       ) AS diff
FROM   STP s
       INNER JOIN ADHH a
       ON ( s.id = a.id )

Результаты:

|      DIFF |
|-----------|
| -00:00:11 |
| +00:00:30 |

Я предложу вам небольшое изменение в вашем запросе

SELECT 
TO_CHAR(TRUNC(x/3600),'FM9900') || ':' ||
TO_CHAR(TRUNC(MOD(x,3600)/60),'FM00') || ':' ||
TO_CHAR(MOD(x,60),'FM00')
 FROM DUAL   

просто замените «x» в приведенном выше запросе столбцом результатов.

24 *  (
to_date(to_char(stp.created_date,'YYYY-MM-DD hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss') -
(to_date(to_char(adhh.created_date,'YYYY-MM-DD hh24:mi:ss'),'YYYY-MM-DDhh24:mi:ss')))

надеюсь это поможет..

Похоже, ваши столбцы уже имеют формат DATE (так и должно быть!). В этом случае вы можете просто получить разницу между датами как DAY TO SECOND INTERVAL. Используйте для этого функцию NUMTODSINTERVAL. Затем, используя функцию EXTRACT, вы можете получить интервал в часах, минутах и ​​секундах.

Вот пример запроса.

WITH
    stp (created_date)
    AS
        (SELECT CAST (TIMESTAMP '2018-02-26 12:59:21' AS DATE) FROM DUAL),
    adhh (created_date)
    AS
        (SELECT CAST (TIMESTAMP '2018-02-26 12:59:32' AS DATE) FROM DUAL)
SELECT    LPAD (EXTRACT (HOUR FROM diff_int), 2, '0')   -- get hours from interval
       || ':'
       || LPAD (EXTRACT (MINUTE FROM diff_int), 2, '0') -- get minutes from interval
       || ':'
       || LPAD (EXTRACT (SECOND FROM diff_int), 2, '0') -- get seconds from interval
           AS diff_hours
FROM (SELECT NUMTODSINTERVAL (  -- get difference between the dates as an interval
                 b.created_date
                 - a.created_date,
                 'DAY'
             )
                 diff_int
      FROM stp a,
           adhh b);

Вот результат.

DIFF_HOURS

00:00:11