Как преобразовать временную метку в число в Oracle SQL

На мой взгляд, «метка времени» хранится в виде строки, обозначающей hours:minutes:seconds. Если это так, то одним из вариантов может быть

• преобразуйте его в секунды, извлекая каждую его часть и умножая на соответствующее значение
• как только у вас появятся секунды, вычислите среднее значение (это нормально, потому что результат будет представлять собой среднее количество секунд)
• преобразовать это значение обратно в секунды, часы и минуты (опять же, применив соответствующий расчет)
• наконец, скомпонуйте результат путем объединения ранее вычисленных значений

Вот пример:

Образец данных; это примерно на 6 минут меньше и больше 2 часов, а это значит, что окончательный результат должен быть «2 часа 0 минут» и «(46 + 18)/2 = 64/2 = 32 секунды».

SQL> with
  2  test (col) as
  3    (select '01:54:46' from dual union all
  4     select '02:06:18' from dual
  5    ),

Запрос начинается здесь:

  6  convert_to_number as
  7    (select col,
  8         to_number(regexp_substr(col, '\d+', 1, 1)) * 60 * 60 +
  9         to_number(regexp_substr(col, '\d+', 1, 2)) * 60 +
 10         to_number(regexp_substr(col, '\d+', 1, 3)) to_num
 11     from test
 12    ),
 13  calculate_average as
 14    (select avg(to_num) avg_tn
 15     from convert_to_number
 16    ),
 17  hms as
 18    (select avg_tn,
 19       avg_tn / (60 * 60) c_hour,
 20       --
 21       mod(avg_tn / (60 * 60), 1) * 60 c_min,
 22       --
 23       mod(mod(avg_tn / (60 * 60), 1) * 60, 1) * 60 c_sec
 24     from calculate_average
 25    )
 26  select lpad(trunc(c_hour), 2, '0') ||':'||
 27         lpad(trunc(c_min) , 2, '0') ||':'||
 28         lpad(trunc(c_sec)  , 2, '0') as result
 29  from hms;

RESULT
--------
02:00:32

SQL>

Длительность в SQL называется INTERVAL. Поэтому естественным способом сделать это было бы сохранить данные в виде интервала (INTERVAL DAY TO SECOND в Oracle), а затем получить среднее значение:

select avg(duration) from mytable where ...

Вы можете преобразовать длительность, хранящуюся в строковом столбце, как «ЧЧ:ММ:СС» с помощью

to_dsinterval('0 ' || duration_string)

Однако Oracle не смогла суммировать и усреднять интервалы до Oracle 21c. Это возможно только начиная с Oracle 23c.

В более старых версиях вместо этого вы конвертировали продолжительность в секунды и работали со следующими:

with
  durations as
  (
    select
      to_number(substr(duration_string, 1, 2)) * 3600 +
      to_number(substr(duration_string, 4, 2)) *   60 +
      to_number(substr(duration_string, 7, 2)) as seconds
    from mytable
    where ...
  )
select numtodsinterval(avg(seconds), 'second')
from durations;

NUMTODSINTERVAL преобразует секунды в INTERVAL DAY TO SECOND, как вы уже догадались. Если вы хотите отображать результат не в виде интервала, а в виде строки в исходном формате, используйте TO_CHAR. К сожалению, эта функция не работает с интервалами в Oracle, поэтому вам придется добавить фиктивную дату:

to_char(date '1900-01-01' + numtodsinterval(avg(seconds), 'second'), 'hh24:mi:ss')

Демо: https://dbfiddle.uk/B2kaYVLw

Вы можете преобразовать каждое значение времени в DATE, а затем найти разницу с DATE в полночь того же дня, усреднить это значение и преобразовать его обратно в свой формат времени:

SELECT TO_CHAR(
         TO_DATE('00:00:00', 'HH24:MI:SS')
         + AVG(TO_DATE(ts, 'HH24:MI:SS') - TO_DATE('00:00:00', 'HH24:MI:SS')),
         'HH24:MI:SS'
       ) AS avg_ts
FROM   table_name

Что для примера данных:

CREATE TABLE table_name (ts) AS
  SELECT '01:54:45' FROM DUAL UNION ALL
  SELECT '01:55:15' FROM DUAL;

Выходы:

рабочий пример

Если значения всегда будут меньше дня, поэтому часовая часть всегда находится между 00 и 23 - что неясно из формулировки вопроса - тогда другой подход на основе даты - преобразовать строку в дату и обратно. на количество секунд в дне, которое представляет собой, используя элемент формата SSSSS, который имеет значение «Секунды после полуночи (0-86399)», сначала в виде строки, а затем в виде числа:

select time_str,
  to_date(time_str, 'HH24:MI:SS') as time_dat,
  to_char(to_date(time_str, 'HH24:MI:SS'), 'SSSSS') as sec_str,
  to_number(to_char(to_date(time_str, 'HH24:MI:SS'), 'SSSSS')) as sec_num
from your_table

(Если вы предоставляете только компоненты времени для to_date(), по умолчанию используется первый день текущего месяца.)

Как только каждая строка преобразуется в число в этом диапазоне, вы можете получить среднее значение:

select
  avg(
    to_number(to_char(to_date(time_str, 'HH24:MI:SS'), 'SSSSS'))
  ) as avg_sec_num
from your_table

А затем преобразуйте это обратно в дату, а затем обратно в строку:

select
  to_char(
    to_date(
      avg(
        to_number(to_char(to_date(time_str, 'HH24:MI:SS'), 'SSSSS'))
      ),
      'SSSSS'
    ),
    'HH24:MI:SS'
  ) as avg_time_str
from your_table

рабочий пример