найти прошедшее время между двумя датами в oracle sql

мне нужно найти разницу между временем в формате чч:мм:сс

select msglog.id,max(msglog.timestamp) enddate,
   min(msglog.timestamp) startdate,
   enddate - startdate
   from MESSAGELOG msglog
   group by id

В приведенном выше запросе msglog.timestamp имеет тип DATE.

Как я могу получить прошедшее время или разницу между временем в правильном формате в оракуле?


sql oracle date-arithmetic
person suni    schedule 13.01.2014    source источник

Ответы (3)


arrow_upward
22
arrow_downward

Когда вы вычитаете два значения DATE, такие как enddate - startdate, вы получаете разницу в днях с десятичной точностью, поэтому, например, 1,5 будет означать 1 1/2 дня или 36 часов. Вы можете преобразовать это в HH:MI:SS, используя много математических вычислений, но более простой способ — преобразовать десятичное значение в значение INTERVAL DAY TO SECOND, используя NUMTODSINTERVAL функция:

  NUMTODSINTERVAL(enddate - startdate, 'DAY')

Вы могли бы подумать, что функция TO_CHAR сможет отформатировать это как HH:MI:SS, но, похоже, это не работает. Вместо этого вы можете использовать EXTRACT и TO_CHAR, чтобы убедиться, что вы получаете ведущие нули:

 TO_CHAR(EXTRACT(HOUR FROM NUMTODSINTERVAL(enddate-startdate, 'DAY')), 'FM00')
   || ':' ||
 TO_CHAR(EXTRACT(MINUTE FROM NUMTODSINTERVAL(enddate-startdate, 'DAY')), 'FM00')
   || ':' ||
 TO_CHAR(EXTRACT(SECOND FROM NUMTODSINTERVAL(enddate-startdate, 'DAY')), 'FM00')

Часть 00 кода формата указывает две цифры с начальным нулем, если это необходимо. Часть FM избавляется от начального пробела в отформатированном результате, который при необходимости зарезервирован для отрицательного знака.

Также обратите внимание, что ваш запрос получает совокупные значения и использует их в том же списке SELECT. Oracle не позволит вам сделать это. Вместо этого попробуйте что-то вроде этого:

WITH StartEndByID AS (
  SELECT
    msglog.id,
    NUMTODSINTERVAL(max(msglog.timestamp) - min(msglog.timestamp), 'DAY') elapsed
  FROM messagelog msglog
  GROUP BY id
)
SELECT
  id,
  TO_CHAR(EXTRACT(HOUR FROM elapsed), 'FM00') || ':' ||
    TO_CHAR(EXTRACT(MINUTE FROM elapsed), 'FM00') || ':' ||
    TO_CHAR(EXTRACT(SECOND FROM elapsed), 'FM00') AS ElapsedHHMISS
FROM StartEndByID
person Ed Gibbs    schedule 13.01.2014
comment
Мне это нравится больше, чем мой ответ, +1. - person GriffeyDog; 13.01.2014
comment
Привет, Эд, просто интересно узнать, работает ли NUMTODSINTERVAL для интервалов меток времени? - person San; 13.01.2014
comment
@San — результат арифметики временных меток в любом случае это interval, поэтому вам не нужно преобразовывать его в единицу. - person Alex Poole; 13.01.2014
comment
Если вы вычтете TIMESTAMP из TIMESTAMP или TIMESTAMP из DATE, Oracle даст вам результат INTERVAL DAY TO SECOND. Вам не нужно конвертировать. Это необходимо только при вычитании DATE из DATE. - person Ed Gibbs; 13.01.2014
comment
Спасибо Эду и Алексу за объяснение. Алекс, мой вопрос заключался в том, имеют ли и end_date, и start_date тип данных TIMESTAMP. Извините, я забыл упомянуть об этом в последнем комментарии... Спасибо. - person San; 13.01.2014

arrow_upward
20
arrow_downward

Арифметика дат в Oracle приводит к числу, выраженному в днях. Итак, чтобы преобразовать в часы, вы должны умножить на 24, а затем trunc, чтобы получить целое число:

trunc(24 * (enddate - startdate))

Чтобы получить минуты, преобразуйте значение дней в минуты и mod() с 60:

mod(trunc(24 * 60 * (enddate - startdate)), 60)

Для секунд снова конвертируйте дни в секунды и mod() с 60:

mod(trunc(24 * 60 * 60 * (enddate - startdate)), 60)

Теперь вы можете объединить их, чтобы получить нужное строковое значение.

person GriffeyDog    schedule 13.01.2014
comment
Спасибо за голос @GriffeyDog и +1 себе! Ваш ответ также будет работать хорошо. Это сводится к большому количеству математики по сравнению с большим количеством вызовов функций, и я ожидаю, что некоторым людям будет удобнее использовать много математики. - person Ed Gibbs; 13.01.2014

arrow_upward
3
arrow_downward

Чтобы получить разницу в секундах, вы можете использовать это

select round(24 * 60 * 60* (TO_DATE('2017/02/17 9:32:25', 'YYYY/MM/DD HH:MI:SS') - TO_DATE('2017/02/17 8:30:30', 'YYYY/MM/DD HH:MI:SS'))) from dual;
person ok2307    schedule 07.03.2017