Oracle SQL To_Char(间隔)不使用我的格式字符串?

Question

在下面的SQL代码中，我想以HH:MM:SS格式打印一个间隔(实际上是秒数)。我将秒数转换为Interval，然后尝试使用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

Answer 1

正如您所观察到的，to_char()不能间隔工作。所以，不要用间隔来思考。相反，您有若干秒，并且希望将其转换为HH:MM:SS格式的字符串。

使用算术和字符串操作:

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<>小提琴

Answer 2

我搜索了其他相关回复;很明显，Oracle实际上并没有为TO_CHAR(DSINTERVAL)实现用户指定的格式。正如一些人建议的那样，您可以添加一个日期，然后使用日期格式。但如果间隔超过24小时，则失败。

我最终使用REGEXP_SUBSTRING()，但随后遇到了Oracle显然不支持非捕获组的问题。我拼凑出了这个:

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

似乎工作得很好。它抑制任何前导零和小数秒。

Answer 3

如果您的间隔总是小于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}')

否则需要使用EXTRACT函数:

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

Answer 4

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". */

输出示例为:"2d 02:59:57.31"

Answer 5

试试这个:

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

NUMTODSINTERVAL的文档说它返回一个字符串字面值，所以to_char不起作用。上面的hack只是获取当前日期/时间(sysdate)，截断HH:mm:ss组件，然后添加您指定的#seconds，然后格式化为只显示HH:mm:ss值