Format Interval with to_char

Question

Next SQL command

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

displays the result of the format: +000000000 00: 03: 01.954000.

Is it possible to introduce a special format in the to_char function to get the result of the format: +00 00: 00: 00.000?

Answer 1

I understand that this is not smart at all, and this is not the special format string you are looking for, but this answer really works, given that the output is a 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 reduces fractional seconds instead of rounding, but I assume from your example they are still zeros.

This is even more embarrassing, but I could not resist:

 SELECT SUBSTR(REPLACE(TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00'))) , '0000000', '') , 1, 16) FROM table1; 

Answer 2

you can apply the result if you want less accuracy:

 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 OP comment:

From Oracle Documentation (11gr1) :

Interval data types do not have format models. Therefore, to customize their presentation, you must combine character functions such as EXTRACT and concatenate components.

It seems you need to manually use EXTRACT to achieve the desired result:

 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 

It is not very elegant, but it seems that this is the only way to cope with the accuracy of microseconds.

Answer 3

to_char () seems to have a fixed format :( therefore regexp_substr may be an option, for example:

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

Answer 4

A minor case of thread necromancy, however, I came across this question while trying to format the 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 a problem if the intervals can be more than one day. There is no format element for days that will give 0. "DDD" is the day of the year, so in the above example there will be 365 or 1 or more if the interval was more than one day. This is normal if your intervals are less than 24 hours.

It should be added that this is at 11g, so it may not apply to the OP.

Answer 5

You can cut the last part (or any part) with the regex Oracle REGEXP_REPLACE does just that.

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

Answer 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 a date and use to_char ('HH24: MI')!