0

Table creation script:

CREATE TABLE "TEST"("INTERVAL_COL" INTERVAL DAY (0) TO SECOND (6));
Insert into TEST (INTERVAL_COL) values ('+00 11:00:24.920000');
Insert into TEST (INTERVAL_COL) values ('+00 11:00:26.890000');
Insert into TEST (INTERVAL_COL) values ('+00 11:00:28.460000');
Insert into TEST (INTERVAL_COL) values ('+00 11:00:30.140000');
Insert into TEST (INTERVAL_COL) values ('+00 11:00:31.790000');
commit;

In both SQL Developer and sqlPlus, running SELECT * FROM TEST works fine, as does SELECT INTERVAL_COL FROM TEST.

But, any of the following give me an error:

SELECT INTERVAL_COL || '.' FROM TEST
SELECT TO_CHAR(INTERVAL_COL, 'HH24:MM.SS') FROM TEST

The error is:

ORA-01877: string is too long for internal buffer

simonalexander2005
  • 4,338
  • 4
  • 48
  • 92
  • 1
    It is odd, and looks like it might be a bug, though not one I can see published. It specifically seems to not like the `day(0)`. You can cast to an unrestricted interval... but you can't format an interval in the same way you can format a date or timestamp anyway, so you either have to manipulate it to get a date/timestamp, or extract the components and build a string from them ([as here](https://stackoverflow.com/a/45601629/266304)). – Alex Poole Apr 14 '20 at 11:23
  • But you would expect `SELECT INTERVAL_COL || '.' FROM TEST` to work out-of-the-box though, right? Is there anywhere I can report this to Oracle? – simonalexander2005 Apr 14 '20 at 12:14
  • You can raise a service request via support.oracle.com, but you'll need an account and a support contract. – Alex Poole Apr 14 '20 at 12:37
  • You pass a string, not an interval. `INSERT INTO TEST (INTERVAL_COL) VALUES (INTERVAL '+00 11:00:24.920000' DAY TO SECOND);` – Wernfried Domscheit Apr 14 '20 at 12:45
  • @WernfriedDomscheit The insert is working fine, and creating a valid interval - that's not the issue – simonalexander2005 Apr 14 '20 at 12:47

2 Answers2

2

If you want to convert this day to second data to the HH24:MI:SS then you can use:

TO_CHAR(CAST((TRUNC(SYSTIMESTAMP)+INTERVAL_COL) AS DATE), 'HH24:MI.SS')

Also as commented by Alex,

TO_CHAR(TRUNC(SYSDATE)+INTERVAL_COL, 'HH24:MI.SS')

will work too.

Popeye
  • 35,427
  • 4
  • 10
  • 31
  • That doesn't seem to work - it's adding 4 minutes to the time: `+00 11:00:24.920000` is output as `11:04.24` – simonalexander2005 Apr 14 '20 at 11:05
  • 1
    ...because it's `MI` not `MM` you want. That then works, thanks - but doesn't explain the error I was getting – simonalexander2005 Apr 14 '20 at 11:09
  • Yes, MI is needed,. Corrected in the answer – Popeye Apr 14 '20 at 11:11
  • 1
    Not sure why you're casting; you can simplify to `TO_CHAR(TRUNC(SYSDATE)+INTERVAL_COL, 'HH24:MI.SS')`, or use a date or timestamp literal instead of trunc'ing. – Alex Poole Apr 14 '20 at 11:22
  • @AlexPoole The cast (but to TIMESTAMP) would be needed if I wanted my format string to be `HH24:MI.SSFF6` though, right? – simonalexander2005 Apr 14 '20 at 12:13
  • 1
    @simonalexander2005 - you have a date though; casting won't restore the fractional seconds. But you could do: `TO_CHAR(TIMESTAMP '2000-01-01 00:00:00' + INTERVAL_COL, 'HH24:MI:SS.FF6')` – Alex Poole Apr 14 '20 at 12:36
0

Despite documented TO_CHAR does not seem to work with INTERVAL values. Output format of INTERVAL is always fix, i.e. it does not depend on current user session NLS-Settings, so you can use RegExp.

Try this one:

REGEXP_REPLACE(REGEXP_SUBSTR(INTERVAL_COL, '\d{2}:\d{2}:\d{2}'), ':(\d{2})$', '.\1')

Note, if the intervals may become greater than 24 hours then result will be wrong (the same applies for solutions like TO_CHAR(CAST((TRUNC(SYSTIMESTAMP)+INTERVAL_COL) AS DATE), 'HH24:MI.SS'))

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • 1
    `to_char` does (usually) work in the sense that you start with an interval and end up with a string; you just can't specify the format. Anyway... this still hits the same error/bug, from the implicit `to_char(interval_col)`. (Unless you `cast(INTERVAL_COL as interval day to second)` which works, but shouldn't be necessary...) On the plus side I suppose the `day(0)` that seems to cause this means it can't go above 24 hours *8-) – Alex Poole Apr 14 '20 at 13:15