format interval with to_char

问题:

Following SQL command

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.

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?

回答1:

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;

回答2:

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

Interval datatypes do not have format models. Therefore, to adjust their presentation, you must combine character functions such as EXTRACT and concatenate the components.

It seems you will have to manually use EXTRACT to achieve the desired output:

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.

回答3:

to_char() seems to have fixed format :( so regexp_substr may be an option, e.g.:

SELECT regexp_substr (TO_DSINTERVAL ('10 10:00:00'), '\d{2} \d{2}:\d{2}:\d{2}\.\d{3}') t_interval FROM dual

回答4:

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 elements for datetime ...

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.

Should add this is on 11g so may well not have be applicable to the OP.

回答5:

You can strip out the last part (or any part) with regular expression Oracle REGEXP_REPLACE does just that.

select REGEXP_REPLACE( TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00'))), '..*') from table1

回答6:

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