format interval with to_char

Question

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?

Disproportionation

Answer 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;

Inspect

your second proposal works fine! also with HQL. Thanks a lot for your help! – Disproportionation

Answer 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.

Introit

Your proposal seems to be very near to the solution I need. On SQL the command is working quiet well, but I need to query it via HQL in hibernate. Using this command hibernate throws following exception: org.hibernate.hql.ast.QuerySyntaxException: expecting CLOSE, found 'DAY' – Disproportionation

I edited my answer: I've never worked with HQL but maybe it is unaware of the interval datatype, therefore you will have to convert it manually. – Introit

Unfortunately hibernate is unaware of it. – Disproportionation

His second solution should work for Hibernate, though - the SQL doesn't require any awareness of the interval datatype, and it's much cleaner than my brute-force string-smashing version. I gave you an upvote because of this. – Inspect

But your solution is more suitable for my problem, because I have cannot create a HQL-query, but it is created by an underlying framework and in this special case it is quiet complicated to use e.g. subqueries or nested queries like vincents solution. – Disproportionation

Answer 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

Bandicoot

In my opinion, this is a very elegant way to go. I would have selected this as the answer, personally. – Prudie

Answer 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.

Carpathoukraine

Answer 5

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

Aholah

It is generally better to add some explanation to your answer to help the OP and future visitors understand why and how your solution works. – Issykkul

Please read How do I write a good answer? before attempting to answer more questions. – Teilo

Answer 6

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

Lastditch

OR USE CAST:TO_CHAR( CAST( <VALUE> AS INTERVAL DAY(2) TO SECOND(0) ) ) – Lastditch

Answer 7

SQL> SELECT
  2      TO_CHAR(TIMESTAMP '1969-12-31 00:00:00' + to_dsinterval('0 00:03:01.954321'),
  3              '"T minus" HH24 "hours" MI "minutes" SS.FF3 "seconds."'
  4  ) AS elapsed_time
  5  FROM
  6      dual;

ELAPSED_TIME                               
-------------------------------------------
T minus 00 hours 03 minutes 01.954 seconds.

Interspace

Code alone makes for a pretty low quality answer. Could you explain us what your code snippet does? – Foreandafter