format interval with to_char

0 votes
asked Jun 9, 2009 by georg-henkel

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?

5 Answers

0 votes
answered Jun 9, 2009 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):

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.

0 votes
answered Jun 9, 2009 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;
0 votes
answered Jun 29, 2010 by tomi

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
0 votes
answered Jun 29, 2014 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 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.

0 votes
answered Jun 2, 2016 by leon

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

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...