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

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

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

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.