SQL 使用 to_char 格式化间隔
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/970249/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
format interval with to_char
提问by Georg Leber
Following SQL command
遵循 SQL 命令
select TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00'))) from table1
produces a result of the format: +000000000 00:03:01.954000.
产生以下格式的结果：+000000000 00:03:01.954000。
Is it possible to enter a special format in the to_char function in order to get a result of format: +00 00:00:00.000?
是否可以在 to_char 函数中输入特殊格式以获得格式的结果：+00 00:00:00.000？
采纳答案by Steve Broberg
I realize it's not clever at all, nor is it the special format string you're looking for, but this answer does work, given that the output is fixed length:
我意识到它一点也不聪明，也不是您正在寻找的特殊格式字符串，但是鉴于输出是固定长度，这个答案确实有效：
SELECT SUBSTR(TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00'))), 1, 1)
|| SUBSTR(TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00'))), 9, 2)
|| ' '
|| SUBSTR(TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00'))), 12, 12)
FROM table1;
It also just truncs the fractional seconds instead of rounding, but I assume from your example they're all just zeros anyway.
它也只是截断小数秒而不是四舍五入，但我假设从你的例子中它们都只是零。
This is an even greater embarrassment, but I couldn't resist:
这是一个更大的尴尬，但我无法抗拒：
SELECT SUBSTR(REPLACE(TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00')))
, '0000000', '')
, 1, 16)
FROM table1;
回答by Vincent Malgrat
you could cast the result if you want less precision:
如果你想要更低的精度，你可以转换结果：
SQL> SELECT TO_DSINTERVAL('10 10:00:00') t_interval FROM dual;
T_INTERVAL
-----------------------------------------------------------
+000000010 10:00:00.000000000
SQL> SELECT CAST(TO_DSINTERVAL('10 10:00:00')
2 AS INTERVAL DAY(2) TO SECOND(3)) t_interval
3 FROM dual;
T_INTERVAL
-----------------------------------------------------------
+10 10:00:00.000
Edit following OP comment:
From The Oracle Documentation (11gr1):
编辑以下 OP 评论：
来自Oracle 文档 (11gr1)：
Interval datatypes do not have format models. Therefore, to adjust their presentation, you must combine character functions such as EXTRACT and concatenate the components.
间隔数据类型没有格式模型。因此，要调整它们的呈现方式，您必须结合 EXTRACT 等字符函数并连接组件。
It seems you will have to manually use EXTRACT to achieve the desired output:
看来您必须手动使用 EXTRACT 来实现所需的输出：
SQL> SELECT to_char(extract(DAY FROM t_interval), 'fmS99999') || ' ' ||
2 to_char(extract(HOUR FROM t_interval), 'fm00') || ':' ||
3 to_char(extract(MINUTE FROM t_interval), 'fm00') || ':' ||
4 to_char(extract(SECOND FROM t_interval), 'fm00.000')
5 FROM (SELECT TO_DSINTERVAL('10 01:02:55.895') t_interval FROM dual)
6 ;
TO_CHAR(EXTRACT(DAYFROMT_INTER
------------------------------
+10 01:02:55.895
This is not very elegant but it seems it is the only way to deal with microseconds precision.
这不是很优雅，但它似乎是处理微秒精度的唯一方法。
回答by tomi
to_char() seems to have fixed format :( so regexp_substr may be an option, e.g.:
to_char() 似乎有固定格式 :( 所以 regexp_substr 可能是一个选项，例如：
SELECT regexp_substr (TO_DSINTERVAL ('10 10:00:00'), '\d{2} \d{2}:\d{2}:\d{2}\.\d{3}') t_interval FROM dual
回答by Jeremy Gosling
Slight case of thread necromancy, however I came across this question while searching for how to format an interval, so I thought it was worth adding this comment.
线程死灵法的轻微情况，但是我在搜索如何格式化间隔时遇到了这个问题，所以我认为值得添加此评论。
From the Oracle documentation, adding a timestamp to an interval results in a timestamp, so by adding a constant timestamp with zero time elements you can then use the standard to_char format elementsfor datetime ...
从Oracle 文档中，将时间戳添加到时间间隔会产生时间戳，因此通过添加具有零时间元素的常量时间戳，您可以使用标准的 to_char格式元素作为日期时间......
SELECT TO_CHAR( TIMESTAMP'1969-12-31 00:00:00' + TO_DSINTERVAL('0 00:03:01.954321'),
'HH24:MI:SS.FF3' ) FROM dual;
However, there is an issue if you intervals could be greater than a day. There is no format element for days that will yield 0. "DDD" is day of the year, so would be 365 in the example above, or 1 or more if the interval was greater then a day. This is fine as long as your intervals are less than 24 hours though.
但是，如果您的时间间隔可能大于一天，则会出现问题。日期没有格式元素会产生 0。“DDD”是一年中的某一天，因此在上面的示例中为 365，如果间隔大于一天，则为 1 或更多。只要您的间隔时间少于 24 小时，这就没问题。
Should add this is on 11g so may well not have be applicable to the OP.
应该在 11g 上添加这个，所以很可能不适用于 OP。
回答by Grigo Port
SELECT W.SHIFT_NUMB || ' c: ' ||
TO_CHAR(TO_DATE('01.01.2012', 'dd.mm.yyyy') + W.TIMEFROM, 'HH24:MI') ||
' по: ' ||
TO_CHAR(TO_DATE('01.01.2012', 'dd.mm.yyyy') + W.TIMETO, 'HH24:MI'),
w.ID
FROM AC_WORK_SHIFT W
WHERE W.CLIENT_ID = GC
Just add date and use to_char ('HH24:MI') !
只需添加日期并使用 to_char ('HH24:MI') ！
回答by Leon
You can strip out the last part (or any part) with regular expression Oracle REGEXP_REPLACE does just that.
您可以使用正则表达式去掉最后一部分（或任何部分） Oracle REGEXP_REPLACE 就是这样做的。
select REGEXP_REPLACE( TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00'))), '..*') from table1
从表 1 中选择 REGEXP_REPLACE( TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00'))), '..*')
