Forum Stats

  • 3,824,835 Users
  • 2,260,429 Discussions
  • 7,896,325 Comments

Discussions

to_char on an interval

John_K
John_K Member Posts: 2,498 Gold Trophy
edited Sep 5, 2019 5:25AM in Database Ideas - Ideas

I'm sure this must have already been raised as an idea, but having searched I couldn't see it. This would be very useful:

select to_char(numtodsinterval(20,'second'),'HH:MI:SS') from dual

(obviously only allow a format mask which make sense for an interval).

currently to achieve such a result it's long-winded.

select to_char(extract(hour from i), 'fm00')||       ':' ||       to_char(extract(minute from i), 'fm00')||       ':' ||       to_char(extract(second from i), 'fm00')         fmt  from (select numtodsinterval(20, 'second') i from dual)
Sven W.KayKMike KutzWilliam Robertsonfac586berxThorsten KettnerEvandro Lima-Oracleulohmannsdstuber
10 votes

Active · Last Updated

Comments

  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    Yes for some reason intervals don't allow to be formatted.

    Here is a crude workaround (forgets the days part of the interval):

    select substr(interval '20' second,5,8) from dual;00:00:20
  • John_K
    John_K Member Posts: 2,498 Gold Trophy
    edited Sep 5, 2019 6:28AM

    Yes for some reason intervals don't allow to be formatted.

    Here is a crude workaround (forgets the days part of the interval):

    select substr(interval '20' second,5,8) from dual;00:00:20

    Is the String format of an interval guaranteed? Or might it change based on NLS settings or something similar? For adhoc code though, that's a helpful tip - unfortunately the thing I'm doing at the moment has both positive and negative intervals, so I'm still into the realms of concatenation.

    I also never realised until just performing a test on this, that day-second interval and and interval literals are represented as strings in different formats

    SQL> ;

      1  select a, b, substr(a, 1) c, substr(b,1) d from (

      2* select interval '20' second a, numtodsinterval(20, 'second') b from dual)

    SQL> /

    A                    B                              C                    D

    -------------------- ------------------------------ -------------------- ------------------------------

    +00 00:00:20.000000  +000000000 00:00:20.000000000  +00 00:00:20.000000  +000000000 00:00:20.000000000

    SQL>

  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    Is the String format of an interval guaranteed? Or might it change based on NLS settings or something similar? For adhoc code though, that's a helpful tip - unfortunately the thing I'm doing at the moment has both positive and negative intervals, so I'm still into the realms of concatenation.

    I also never realised until just performing a test on this, that day-second interval and and interval literals are represented as strings in different formats

    SQL> ;

      1  select a, b, substr(a, 1) c, substr(b,1) d from (

      2* select interval '20' second a, numtodsinterval(20, 'second') b from dual)

    SQL> /

    A                    B                              C                    D

    -------------------- ------------------------------ -------------------- ------------------------------

    +00 00:00:20.000000  +000000000 00:00:20.000000000  +00 00:00:20.000000  +000000000 00:00:20.000000000

    SQL>

    John_K wrote:Is the String format of an [ds]interval was guaranteed? ...

    I thought so, but you proved it is not. I'm not sure what influences it thou. I get the same formatting in SQL Developer for both variantes.

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown
    John_K wrote:Is the String format of an [ds]interval was guaranteed? ...

    I thought so, but you proved it is not. I'm not sure what influences it thou. I get the same formatting in SQL Developer for both variantes.

    Column A is INTERVAL DAY(2) TO SECOND(6)

    Column B is INTERVAL DAY(9) TO SECOND(9)

    For the OP, try to CAST the data to something reasonable

    CAST( val AS INTERVAL DAY(1) TO SECOND(0) )

    MK

    William Robertson
  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    Column A is INTERVAL DAY(2) TO SECOND(6)

    Column B is INTERVAL DAY(9) TO SECOND(9)

    For the OP, try to CAST the data to something reasonable

    CAST( val AS INTERVAL DAY(1) TO SECOND(0) )

    MK

    I cast first and then substr. I agree a supported to_char formatter would be useful though.