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?

Vincent Malgrat · Answer 1 · 2014-10-21T09:27:55.990

21

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.

edited Oct 21 '14 at 09:27

answered Jun 09 '09 at 14:42

Vincent Malgrat

66,725
9
119
171

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' – Georg Leber Jun 09 '09 at 15:14
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. – Vincent Malgrat Jun 09 '09 at 16:02
Unfortunately hibernate is unaware of it. – Georg Leber Jun 09 '09 at 16:12
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. – Steve Broberg Jun 09 '09 at 16:22
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. – Georg Leber Jun 09 '09 at 16:47

score 9 · Answer 2 · edited Feb 09 '12 at 17:09

9

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

edited Feb 09 '12 at 17:09

musefan

47,875
21
135
185

answered Nov 29 '10 at 12:24

tomi

99
1
1

In my opinion, this is a very elegant way to go. I would have selected this as the answer, personally. – luis.espinal Apr 13 '18 at 13:47

score 5 · Accepted Answer · edited Jan 19 '17 at 08:50

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;

your second proposal works fine! also with HQL. Thanks a lot for your help! — Georg Leber, Jun 09 '09 at 16:04

score 3 · Answer 4 · answered Oct 29 '14 at 10:53

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.

Grigo Port · Answer 5 · 2018-03-22T16:46:51.320

1

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

edited Mar 22 '18 at 16:46

answered Mar 21 '18 at 15:53

Grigo Port

21
2

2

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. – Anthony Horne Mar 21 '18 at 16:22
Please read [How do I write a good answer?](http://stackoverflow.com/help/how-to-answer) before attempting to answer more questions. – May 09 '18 at 18:09

score 0 · Answer 6 · answered Feb 02 '16 at 17:20

0

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

answered Feb 02 '16 at 17:20

Leon

198
1
7

1

OR USE CAST:TO_CHAR( CAST( AS INTERVAL DAY(2) TO SECOND(0) ) ) – Leon Feb 02 '16 at 18:06

score 0 · Answer 7 · edited Sep 23 '21 at 23:16

0

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.

edited Sep 23 '21 at 23:16

PM 77-1

12,933
21
68
111

answered Sep 23 '21 at 20:39

user16988200

1

Code alone makes for a pretty low quality answer. Could you explain us what your code snippet does? – Simas Joneliunas Sep 24 '21 at 03:29

format interval with to_char

7 Answers7

Linked

Related