Блог

В следующем фрагменте SQL я хочу напечатать интервал (на самом деле несколько секунд) в формате HH:MM:SS. Я преобразую секунды в интервал, а затем пытаюсь использовать форматирование даты TO_CHAR. Сервер Oracle игнорирует мой шаблон и печатает целую метку времени с нулевыми символами в датах и микросекундах. Что я делаю не так?

 CASE WHEN (TR.RUN_LENGTH > 0) THEN TO_CHAR(NUMTODSINTERVAL(TR.RUN_LENGTH,'second'), 'HH24:MI:SS')
ELSE '0' END AS RUN_LENGTH,
 

И я получаю: 000000000 00:03:22.000000000

Как вы заметили, to_char() не работает с интервалами. Так что не думайте в терминах интервалов. Вместо этого у вас есть несколько секунд, и вы хотите преобразовать их в строку в формате ЧЧ:ММ:СС.

Используйте арифметические и строковые операции:

 select lpad(floor(tr.run_length / 3600), 2, '0') || ':' || lpad(mod(floor(tr.run_length / 60),  60), 2, '0') || ':' || lpad(mod(tr.run_length, 60), 2, '0') as hhmmss
from (select 1 as run_length from dual union all
      select 10 as run_length from dual union all
      select 100 as run_length from dual union all
      select 1000 as run_length from dual union all
      select 10000 as run_length from dual union all
      select 100000 as run_length from dual 
     ) tr;
 

Вот скрипка db<>.

Я просмотрел другие связанные ответы; Ясно, что Oracle на самом деле не реализует указанный пользователем формат для TO_CHAR(DSINTERVAL). Как некоторые предположили, вы можете добавить дату, а затем использовать форматирование даты. Но это не удается, если интервал составляет более 24 часов.

В итоге я использовал REGEXP_SUBSTRING (), но затем столкнулся с проблемой, что Oracle, очевидно, не поддерживает группы без захвата. Я смог собрать воедино это:

 REGEXP_SUBSTR(TO_CHAR(NUMTODSINTERVAL(TR.RUN_LENGTH,'second')), '^([ 0: ]*)(.*)(.)',1,1,NULL,2 )
 

что, кажется, работает довольно хорошо. Он подавляет любые начальные нули и доли секунды.

Если ваш интервал всегда меньше 24 часов, то вы можете использовать один из следующих:

 TO_CHAR(TRUNC(SYSDATE)   NUMTODSINTERVAL(TR.RUN_LENGTH, 'second'), 'hh24:mi:ss')

REGEXP_SUBSTR(NUMTODSINTERVAL(TR.RUN_LENGTH,'second'), 'd{2}:d{2}:d{2}')
 

В противном случае вам необходимо использовать функцию ИЗВЛЕЧЕНИЯ:

 SELECT
    TO_CHAR(
       EXTRACT(DAY FROM NUMTODSINTERVAL(TR.RUN_LENGTH,'second')) * 24 
         EXTRACT(HOUR FROM NUMTODSINTERVAL(TR.RUN_LENGTH,'second')) )
   || ':' EXTRACT(MINUTE FROM NUMTODSINTERVAL(TR.RUN_LENGTH,'second') 
   || ':' TRUNC(EXTRACT(SECOND FROM NUMTODSINTERVAL(TR.RUN_LENGTH,'second'))
 

При желании вы должны добавить LPAD(..., 2, '0') вокруг деталей, если это требуется для вашего вывода.

Попробуй это:

 TO_CHAR (TRUNC (SYSDATE)   NUMTODSINTERVAL ((TR.RUN_LENGTH, 'second'),   
    'hh24:mi:ss' )
 

В документе для NUMTODSINTERVAL говорится, что он возвращает строковый литерал, поэтому to_char не имеет никакого эффекта. Этот хак выше просто берет текущую дату/время (sysdate), усекает компонент HH:mm:ss, затем добавляет заданные вами #секунды, а затем форматирует их, чтобы просто отобразить значение HH:MM:SS