Oracle SQL To_Char(interval) is not using my format string?

user_1290828 0
Viewed 320

In the following bit of SQL, I want to print an interval (actually a number of seconds) in HH:MM:SS format. I convert the seconds to an Interval, then attempt to use TO_CHAR date formatting. The Oracle server ignores my pattern and prints a whole timestamp with zero chars in dates and microseconds. What am I doing wrong?

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

And I get: +000000000 00:03:22.000000000

oracle sql
5 Answers

As you have observed, to_char() doesn't work on intervals. So, don't think in terms of intervals. Instead, you have a number of seconds and you want to convert to a string in the format of HH:MM:SS.

Use arithmetic and string operations:

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;

Here is a db<>fiddle.

user_11440350

I searched through the other Related responses; It's clear that Oracle doesn't actually implement a user specified format for TO_CHAR(DSINTERVAL). As some have suggested, you can add a date then use the date formatting. But that fails if the interval is more than 24 hours.

I ended up using REGEXP_SUBSTRING() but then ran into the problem that Oracle evidently doesn't support non-capturing groups. I was able to piece together this:

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

which seems to work quite well. It suppresses any leading zeros and fractional seconds.

user_12908280

If your interval is always less than 24 hours, then you can use one of these:

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}')

Otherwise you need to use the EXTRACT function:

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'))

Optionally you have to add LPAD(..., 2, '0') around the parts, if required for your output.

user_30272660

Less code:

to_number(
substr(to_char((LATER_timestamp - EARLIER_timestamp),'HH24:MI:SS'), 1,10) /* this is the DAY  string */ 
) || 'd ' ||
substr(to_char((LATER_timestamp - EARLIER_timestamp),'HH24:MI:SS'),12,11) /* this is the TIME string.  Milliseconds are "FLOORED". */

Example output is: "2d 02:59:57.31".

user_194557420

Try this:

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

The doc for NUMTODSINTERVAL says it returns a string literal, so to_char has no effect. This hack above just takes the current date/time (sysdate), truncates the HH:mm:ss component, then adds back the #seconds you specify, then format that to just show the HH:MM:SS value

user_17455440
Related